Overview
ISO/IEC 19075-6:2021 provides guidance on SQL support for JavaScript Object Notation (JSON). It explains how SQL databases can store, publish and query JSON, defines the SQL/JSON data model, and specifies the SQL/JSON path language and associated functions. The standard targets consistent handling of JSON as a first-class data type inside the SQL ecosystem, enabling interoperable, predictable behavior across implementations.
Key topics and technical requirements
- Scope: Explicit guidance for storing JSON data, publishing (producing) JSON output, querying JSON, and the SQL/JSON data model and path language.
- SQL/JSON data model: Defines SQL/JSON items (atomic values, arrays, objects) and sequences, and relates JSON text to SQL representations.
- Parsing and serialization: Rules for converting between JSON text and SQL/JSON items.
- Core SQL/JSON functions:
- Query functions: JSON_EXISTS, JSON_VALUE, JSON_QUERY, JSON_TABLE (including nested COLUMNS and PLAN clauses).
- Constructor functions and predicates: JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG, and ISJSON.
- SQL/JSON path language: Syntax and semantics for path expressions, accessors, item methods (type(), size()), filter expressions, arithmetic and comparison operators, plus modes (e.g., strict vs lax) that affect evaluation and error handling.
- Behavioral rules: Handling of JSON nulls vs SQL NULL, PASSING clause for parameter binding, ON ERROR / ON EMPTY behavior, and output formatting options.
- Conformance features: Defined feature sets for implementers to ensure consistent behavior.
Practical applications and users
- Database vendors and implementers - to design and conform SQL engines that support JSON consistently.
- Application developers and data engineers - to construct portable SQL that queries, transforms and emits JSON reliably.
- DBAs and architects - to choose storage and indexing strategies for hybrid relational and semi-structured data.
- System integrators - to standardize JSON ingestion, export, and interoperability across services.
Common use cases:
- Ingesting and storing JSON documents in SQL tables.
- Generating JSON from relational data for APIs.
- Querying nested, semi-structured JSON within SQL queries (filtering, projecting, aggregating).
- Mapping JSON to relational columns using JSON_TABLE for reporting and transformation.
Related standards
- ISO/IEC 9075 (SQL family) - the broader SQL standard series.
- JSON specifications (ECMA-404, RFC 8259) - normative references for JSON text and syntax.
Keywords: ISO/IEC 19075-6, SQL JSON support, SQL/JSON path, JSON_TABLE, JSON_VALUE, JSON_EXISTS, JSON in SQL, database JSON guidance.
Open preview (PDF)Information technology — Guidance for the use of database language SQL — Part 6: Support for JSON
