JSON Array
container.object.json_arrayJSON array format: [val1, val2, val3, ...]. May contain objects, scalars, or nested arrays.
Try a value
Use it
finetype infer -i '[1, 2, 3]' --mode column
# → container.object.json_arraySELECT ft_infer('[1, 2, 3]');
-- → 'container.object.json_array'CAST({col} AS JSON)Pattern
^\[.*\]$| Value | Length | Shape |
|---|---|---|
| [1, 2, 3] | 9 | matches |
| ["a", "b", "c"] | 15 | matches |
| [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}] | 54 | matches |
| [1, 2, 3 | 8 | rejected: one character short |
In Meridian's datasets
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]The JavaScript Object Notation (JSON) Data Interchange Formatstandard
- [2]ECMA-404 - Ecma Internationalcatalogue entry
- [3]PostgreSQL: Documentation: 18: 8.14. JSON Typesdocumentation
- [4]JSON Processing Functions – DuckDBdocumentation
- [5]JSON Overview – DuckDBdocumentation
How it's detected
JSON arrays stored as VARCHAR. Recursive inference may fail if array elements are heterogeneous (mixed types). In such cases, returns LIST<JSON> (untyped list).
Schema
https://meridian.online/schemas/container.object.json_array · Frictionless type array
finetype taxonomy container.object.json_array -o json-schema