Skip to content

Nested Elements

Jump to bottom
Chris edited this page Jan 3, 2024 · 9 revisions

Nested elements are a powerful feature of Export Sheet Data. They allow you to export more complicated data structures and nest various cell values under a single parent element (like a JSON array or XML element with children).

Only JSON supports nested elements currently, but XML support is being worked on!

1. Using Nested Elements
2. Key Formatting
3. Advanced Key Formatting
4. Limitations
5. Examples

Using Nested Elements

To use nested elements, simply check the "Nested Elements" checkbox under "General Options".

If you export your data after enabling nested elements for the first time, you will likely notice that the exported data seems unchanged. This is because nested elements require additional formatting for key values. This extra formatting tells ESD how each nested element should be set up and exported.

Key Formatting Basics for Nested Elements

There are numerous ways to format keys for nested element support. All nested formatting is wrapped in either braces ({ }) or brackets ([ ]). A good rule of thumb is brackets mean arrays and braces mean objects or array elements.

Value

FieldId

This is the most basic nested element type. This element has no nesting and will simply be created in the sheet's root element. It is essentially the same as not using nested elements.

Arrays

[ArrayId]FieldId

A nested array is declared by wrapping an ID in brackets. This particular formatting will create an array called "ArrayId" with an object containing a field called "FieldId".

Objects

{ObjectId}FieldId

A nested object is declared by wrapping an ID in braces. This particular formatting will create an object called "ObjectId" with a field called "FieldId".

Key Structures

All nested element keys follow the basic structure of subpath1/subpath2/.../subpathN/FieldId where 'FieldId' is the name of the field you want to create. The following are all examples of possible keys (specific formatting is discussed in more detail in the next section):

[Planets]{#1}
[Cars]{#Model}Year
{#SHEET}{#ROW}[Spells]{#Fireball}Damage
[Countries]{#Country}[Regions]{#Region}Town

Key Formatting

With the basics of key formatting covered, let's examine some more advanced formatting.

Array Index

[ArrayId]{#Number}

You can manually specify an element in an array to target when nesting elements in an array. This is done by adding a number preceded by the hash symbol (#) and wrapped in braces. For example: {#1} will target the first element in an array while {#3} will target the third element. If there are not enough elements in the targeted array, new empty object elements will be created until the specified index is reached.

Example:

Sheet name: Planets

[planets]{#1} [planets]{#2} [planets]{#3} [planets]{#4} [planets]{#5} [planets]{#6} [planets]{#7} [planets]{#8}
Mercury Venus Earth Mars Jupiter Saturn Uranus Neptune

JSON:

{
  "planets": [
    "Mercury",
    "Venus",
    "Earth",
    "Mars",
    "Jupiter",
    "Saturn",
    "Uranus",
    "Neptune"
  ]
}

Array Index Search

[ArrayId]{#Number}FieldId

This is similar to the array index formatting, except the targeted element will be an object with a field called "FieldId" instead of the array element at the given index.

Example:

Sheet name: Planets

[planets]{#1}Name [planets]{#2}Name [planets]{#3}Name [planets]{#4}Name [planets]{#5}Name [planets]{#6}Name [planets]{#7}Name [planets]{#8}Name
Mercury Venus Earth Mars Jupiter Saturn Uranus Neptune

JSON:

{
  "planets": [
    {
      "Name": "Mercury"
    },
    {
      "Name": "Venus"
    },
    {
      "Name": "Earth"
    },
    {
      "Name": "Mars"
    },
    {
      "Name": "Jupiter"
    },
    {
      "Name": "Saturn"
    },
    {
      "Name": "Uranus"
    },
    {
      "Name": "Neptune"
    }
  ]
}

Array Content Searches

[ArrayId]{#SearchFieldId}FieldId

It's possible to target an array element that contains a specific field with a specific value. To do this, add a field ID preceded by the hash symbol (#). The SearchFieldId should be a key that you have already used in this particular sheet. The value that will be matched is the value of that field in this particular row.

NOTE: Any field searched for this way should have had its value set in one of the cells to the left of this particular field and its nesting must match this key's, or unexpected formatting may occur!

Advanced Key Formatting

Row Indexing

{#ROW}

The ROW key tells ESD to target an array index specific to the current row. Use this when you want to export nested data with row specific associations, often within array elements. The following would allow you to have row specific data contained in object elements below an array:

Sheet name: Ships

[ships]{#ROW}Name [ships]{#ROW}MaidenVoyageDate [ships]{#ROW}Captain
Enterprise (NX-01) 2153 Archer
Enterprise (NCC-1701) 2245 Kirk
Enterprise (NCC-1701-D) 2363 Picard

JSON:

{
  "ships": [
    {
      "Name": "Enterprise (NX-01)",
      "MaidenVoyageDate": 2153,
      "Captain": "Archer"
    },
    {
      "Name": "Enterprise (NCC-1701)",
      "MaidenVoyageDate": 2245,
      "Captain": "Kirk"
    },
    {
      "Name": "Enterprise (NCC-1701-D)",
      "MaidenVoyageDate": 2363,
      "Captain": "Picard"
    }
  ]
}

This is also used after the SHEET key to indicate a sheet array when exporting both sheet arrays and nested elements.

SHEET

{#SHEET}

The SHEET key lets ESD know it should target the root sheet element. This is most commonly used with Row Indexing when exporting both sheet arrays and nested elements.

Exporting Nested Elements and Sheet Arrays at the Same Time

{#SHEET}{#ROW}...

Because of how nested elements are parsed, they cannot really work with sheet arrays out of the box. If you want to export both sheet arrays and nested elements (or sheet arrays containing nested elements) you will have to do some extra formatting for the sheet array keys. Specifically, all sheet array keys will have to be prefaced with {#SHEET}{#ROW}.

Shorthand

[ArrayId]{#FieldId} or {ObjectId}{#FieldId}

If no FieldId follows a content search key, ESD will assume the last key in the nested path is the intended FieldId. For example:

[Artists]{#Name}

Will create a field called 'Name' inside the first element of an array called 'Artists' that does not contain a field called 'Name' already and set its value to the 'Name' field of the row.

Implicit Association

It is not always necessary to indicate the full path to a nested field. If a previous key has shared some of the same nesting path, those indexes will be cached. For example:

[Colors]{#ColorName}R, [Colors]G, [Colors]B

All three of these fields will target the same nested element. (An object with a field 'ColorName' with value matching this row's 'ColorName' field that is an element of an array called 'Colors') The cached values will be kept until another key changes them. This can be used to cut down on redundant key formatting. For example:

[Colors]{#ColorName}R, [Colors]{#ColorName}G, [Colors]{#ColorName}B

Can be shortened to:

[Colors]{#ColorName}R, [Colors]G, [Colors]B

Limitations

The following are the current limitations for nested elements in ESD:

  • XML nesting not supported (being worked on)
  • Cannot have fields with the same ending key name (ie cannot have [fields]{#ID} and [fields]{#ID}[subfields]{#ID} currently
  • Array index caching will break if an object field is used (ie [array]{objectId} will throw an error, but [array]{#searchId}{objectId} will work as expected)
  • Cannot create empty arrays or empty objects

These issues are not permanent will eventually be fixed.

Examples

An example sheet showing a variety of nested element formatting can be viewed here.

Clone this wiki locally