MeridianMERIDIAN

JSON Object

container.object.json

JavaScript Object Notation (JSON) object stored as VARCHAR. Represents structured key-value data that may be nested.

Casts to
JSON
Standard

Shape

Pattern
^\{.*\}$
ValueLengthShape
{"user": "john", "age": 30}27matches
{"name": "Alice", "email": "alice@example.com", "active": true}63matches
{"nested": {"key": "value"}}28matches
{"user": "john", "age": 3026rejected: one character short

Told apart

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

Background

Origin

Douglas Crockford originally specified the JSON format in the early 2000s.[1] JSON grew out of a need for a real-time server-to-browser session communication protocol without using browser plugins such as Flash or Java applets.[1] The acronym JSON originated at State Software, a company cofounded by Crockford and others in March 2001.[1] JSON is based on a subset of the JavaScript Programming Language Standard ECMA-262 3rd Edition - December 1999.[2]

JSON.org describes JSON as built on two structures: a collection of name/value pairs and an ordered list of values.[2] JSON.org notes that in various languages the collection of name/value pairs is realised as an object, record, struct, dictionary, hash table, keyed list or associative array.[2] JSON.org defines an object as an unordered set of name/value pairs that begins with a left brace and ends with a right brace.[2]

Ecma International published the first edition of ECMA-404 in October 2013.[3] The second edition of ECMA-404, The JSON data interchange syntax, is dated December 2017.[3] ECMA-404 carries the ISO/IEC number 21778.[3] RFC 8259, edited by T. Bray and published by the IETF in December 2017, obsoletes RFC 7159.[4] RFC 8259 is the current version of the Internet Standard STD 90.[1] ECMA-404 states that its goal is only to define the syntax of valid JSON texts, not to provide any semantics or interpretation of them.[3]

Use

Under RFC 8259, an object is a pair of curly brackets surrounding zero or more name/value pairs, in which each name is a string followed by a single colon.[4] RFC 8259 says the names within an object SHOULD be unique.[4] RFC 8259 warns that when names within an object are not unique, the behaviour of software receiving the object is unpredictable.[4] RFC 8259 observes that many implementations report only the last name/value pair for a repeated name, while others report an error or report every pair.[4] Python's json module by default ignores all but the last name-value pair for a repeated name.[5] RFC 8259 notes that JSON parsing libraries differ as to whether they make the ordering of object members visible to calling software.[4] Treat key order as meaningless and duplicate keys as a data-quality problem to report, rather than trusting whichever value your parser happens to keep. (Meridian's view)

RFC 8259 requires JSON text exchanged between systems outside a closed ecosystem to be encoded in UTF-8.[4]

In DuckDB, the JSON logical type is physically stored as a VARCHAR, with the restriction that the text must be valid JSON.[6] DuckDB's documentation shows that whitespace and key order are significant when two JSON values are compared.[6] DuckDB allows duplicate keys in JSON objects.[6] DuckDB can cast a JSON value to its STRUCT type, as in '{"duck": 42}'::JSON::STRUCT(duck INTEGER).[6] PostgreSQL offers two types for JSON data, json and jsonb.[7] PostgreSQL's json type stores an exact copy of the input text, preserving key order and keeping duplicate keys.[7] PostgreSQL's jsonb type does not preserve the order of object keys and keeps only the last value of a duplicated key.[7] PostgreSQL's documentation says most applications should prefer to store JSON data as jsonb.[7]

Sources

  1. [1]JSON - Wikipediasecondary source
  2. [2]JSONsecondary source
  3. [3]ECMA-404 - Ecma Internationalcatalogue entry
  4. [4]The JavaScript Object Notation (JSON) Data Interchange Formatstandard
  5. [5]json — JSON encoder and decoder — Python 3.15.0 documentationdocumentation
  6. [6]JSON Type – DuckDBdocumentation
  7. [7]PostgreSQL: Documentation: 18: 8.14. JSON Typesdocumentation

Detection notes

v1 migration: New for v2. JSON objects stored as VARCHAR strings. CRITICAL: Classification triggers recursive field-level inference. DuckDB JSON type allows NULL, arrays, objects, and scalar values.

Use it

CLI · DuckDB
$ finetype infer -i "{"user": "john", "age": 30}" --mode column
→ container.object.json

SELECT ft_infer('{"user": "john", "age": 30}');
-- → 'container.object.json'
Cast expression
CAST({col} AS JSON)

Schema

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

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

Aliases

json_object

Type Registry