Nested JSON and arrays

APIs and document databases rarely store flat rows. A json field groups child fields into an object, or into a list of objects with mode: array.

version: "1"
output: { language: eng, format: json, quantity: 1, options: { pretty: true } }
fields:
  - { name: customer_id, type: uuid }
  - name: address
    type: json
    fields:
      - { name: street, type: text, category: street }
      - { name: city, type: text, category: city }
      - { name: postcode, type: postcode, country: US }
  - name: orders
    type: json
    mode: array
    minItems: 1
    maxItems: 3
    fields:
      - { name: order_id, type: autoint }
      - { name: total, type: decimal, min: 10, max: 500, precision: 2 }
      - { name: status, type: enum, values: [paid, shipped, refunded] }

[
  {
    "address": { "city": "Vancouver", "postcode": "01038", "street": "School Lane" },
    "customer_id": "71d64c2f-4cb5-44a2-a577-9e60912231fb",
    "orders": [
      { "order_id": 1, "status": "paid", "total": 449.41 },
      { "order_id": 2, "status": "shipped", "total": 459.51 }
    ]
  }
]

How it behaves

• Child fields are ordinary fields. They take the same types and options as top-level fields, and can nest further json fields. Catalog types are the exception: they must stay top-level.
• Each object is its own scope. Templates and relational constraints inside orders can refer to earlier siblings in orders, not to customer_id.
• Arrays vary in length. Each record gets between minItems and maxItems items; both default to 1.
• Sequences run across records. An autoint inside an array keeps counting through the whole job, so order_id is unique across all customers, not restarted per customer.
• Cost counts top-level fields. The example is 3 fields per record, however many orders each customer has.

In other formats

JSON and YAML keep the structure. CSV, TSV, XLSX, XML, SQL and the others write each object or array as compact JSON text in one cell:

customer_id,address,orders
9ea561d7-…,"{""city"":""Toronto"",""postcode"":""90327"",""street"":""Center Street""}","[{""order_id"":1,""status"":""paid"",""total"":409.06}]"

That suits a JSON column in a database. When a table needs one column per value, declare the values as separate top-level fields instead.