MeridianMERIDIAN

JSON Array

container.object.json_array

JSON array format: [val1, val2, val3, ...]. May contain objects, scalars, or nested arrays.

Casts to
JSON
Standard

Shape

Pattern
^\[.*\]$
ValueLengthShape
[1, 2, 3]9matches
["a", "b", "c"]15matches
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]54matches
[1, 2, 38rejected: one character short

Told apart

1 of the 6 other object types accept every one of these samples on shape alone.

Background

Origin

RFC 8259 defines an array in JSON as an ordered sequence of zero or more values.[1] RFC 8259 says the terms "object" and "array" come from the conventions of JavaScript.[1] RFC 8259 describes JSON as derived from the object literals of JavaScript, as defined in the ECMAScript Programming Language Standard, Third Edition.[1] RFC 8259 gives JSON's design goals as being minimal, portable, textual and a subset of JavaScript.[1]

RFC 8259 says RFC 4627 originally described JSON and registered the media type application/json.[1] Ecma International publishes JSON as ECMA-404, The JSON data interchange syntax, whose 2nd edition is dated December 2017.[2] ECMA-404's 1st edition was published in October 2013.[2] ECMA-404 is also numbered ISO/IEC 21778.[2]

RFC 8259 writes an array as square brackets surrounding zero or more values, with the elements separated by commas.[1] RFC 8259 places no requirement that the values in an array be of the same type.[1]

Use

PostgreSQL's json data type stores an exact copy of the input text, preserving semantically-insignificant white space between tokens.[3] PostgreSQL's documentation says most applications should generally prefer to store JSON data as jsonb.[3] In PostgreSQL's jsonb containment test, the order of array elements is not significant, so '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb is true.[3]

DuckDB's json_structure function defaults to JSON when the structure is inconsistent, such as incompatible types in an array.[4] DuckDB's documentation gives json_structure('["duck", {"family": "anatidae"}]') as returning ["JSON"].[4] DuckDB's json_array_length function returns the number of elements in a JSON array, or 0 if the value is not a JSON array.[4] DuckDB uses 1-based indexing for its ARRAY and LIST data types but 0-based indexing for the JSON data type, following PostgreSQL's conventions.[5] When an array column lands in a typed list, check whether every index you carried over from the JSON side needs shifting by one. (Meridian's view)

RFC 8259 allows a JSON parser to set limits on the maximum depth of nesting and on the size of texts it accepts.[1] Long numeric identifiers inside an array are safer kept as strings, or parsed with a decimal or 64-bit integer type, than read as floating point. (Meridian's view) A bracketed value in a CSV column is not always JSON: single-quoted lists and other near-JSON forms can look much the same, so validate with a real JSON parser rather than a pattern that only checks the brackets. (Meridian's view)

Sources

  1. [1]The JavaScript Object Notation (JSON) Data Interchange Formatstandard
  2. [2]ECMA-404 - Ecma Internationalcatalogue entry
  3. [3]PostgreSQL: Documentation: 18: 8.14. JSON Typesdocumentation
  4. [4]JSON Processing Functions – DuckDBdocumentation
  5. [5]JSON Overview – DuckDBdocumentation

Detection notes

v1 migration: New for v2. JSON arrays stored as VARCHAR. Recursive inference may fail if array elements are heterogeneous (mixed types). In such cases, returns LIST<JSON> (untyped list).

Use it

CLI · DuckDB
$ finetype infer -i "[1, 2, 3]" --mode column
→ container.object.json_array

SELECT ft_infer('[1, 2, 3]');
-- → 'container.object.json_array'
Cast expression
CAST({col} AS JSON)

Schema

https://meridian.online/schemas/container.object.json_array · Frictionless type array

finetype taxonomy container.object.json_array -o json-schema

Aliases

json_listarray

Type Registry