ISO/IEC 19075-6:2021

Information technology - Guidance for the use of database language SQL - Part 6: Support for JSON

This document describes the support in SQL for JavaScript Object Notation. This document discusses the following features of the SQL language: - Storing JSON data. - Publishing JSON data. - Querying JSON data. - SQL/JSON data model and path language.

Technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 6: Support pour JSON

General Information

• Status: Published
• Publication Date: 31-Aug-2021
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 6060 - International Standard published
• Start Date: 01-Sep-2021
• Due Date: 11-Feb-2024
• Completion Date: 31-Aug-2021

Relations

• Consolidated By: ISO/FDIS 22477-6 - Geotechnical investigation and testing - Testing of geotechnical structures - Part 6: Load testing of soil nails and rock bolts
• Effective Date: 18-Nov-2023
• Revises: ISO/IEC TR 19075-6:2017 - Information technology - Database languages - SQL Technical Reports - Part 6: SQL support for JavaScript Object Notation (JSON)
• Effective Date: 23-Apr-2020

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.

Standards Content (Sample)

INTERNATIONAL ISO/IEC
STANDARD 19075-6
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 6:
Support for JSON
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 6: Support pour JSON
Reference number
©
ISO/IEC 2021
© ISO/IEC 2021
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved

ISO/IEC19075-6:2021(E)
Contents Page
Foreword.ix
Introduction.xi
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 JavaScriptObjectNotation(JSON).6
4.1 ContextforJSON.6
4.2 WhatisJSON?.6
4.3 RepresentationsofJSONdata.7
4.3.1 IntroductiontorepresentationsofJSONdata.7
4.3.2 Avro.7
4.3.3 BSON.8
4.4 Schemas.8
4.4.1 JSONschemataandvalidity.8
4.4.2 Avroschemata.9
4.4.3 BSONschemata.9
4.5 WhydoesJSONmatterinthecontextofSQL?WhatisJSON’srelationshiptoNoSQL?.10
4.6 UsecasesforJSONsupportinSQL.11
4.6.1 Introductiontotheusecases.11
4.6.2 JSONdataingestionandstorage.11
4.6.3 JSONdatagenerationfromrelationaldata.11
4.6.4 QueryingJSONaspersistentsemi-structureddatamodelinstances.12
4.7 Whatfeaturesaddressthoseusecases?.12
4.7.1 Addressingtheusecases.12
4.7.2 StoringJSONdatainanSQLtable.12
4.7.3 GeneratingJSONinanSQLquery.12
4.7.4 QueryingJSONdatainSQLtablesusingSQL.13
5 TheSQL/JSONdatamodel.14
5.1 IntroductiontotheSQL/JSONdatamodel.14
5.2 SQL/JSON items.15
5.2.1 DefinitionofSQL/JSONitems.15
5.2.2 Atomic values.16
5.2.3 SQL/JSONarrays.17
5.2.4 SQL/JSON objects.17
5.3 SQL/JSON sequences.17
5.4 Parsing JSON.18
5.5 SerializingJSON.18
6 SQL/JSONfunctions.19
©ISO/IEC2021–Allrightsreserved iii

ISO/IEC19075-6:2021(E)
6.1 IntroductiontoSQL/JSONfunctions.19
6.2 HandleJSONusingbuilt-infunctions.19
6.3 JSONAPIcommonsyntax.19
6.3.1 IntroductiontoJSONAPIcommonsyntax.19
6.3.2 JSONvalueexpression.20
6.3.3 Pathexpression.20
6.3.4 PASSING clause.20
6.3.5 JSONoutputclause.21
6.3.6 ONERRORandONEMPTYsyntax.21
6.4 Queryfunctions.22
6.4.1 Thefourqueryfunctions.22
6.4.2 JSON_EXISTS.22
6.4.3 JSON_VALUE.25
6.4.4 JSON_QUERY.29
6.4.5 JSON_TABLE.33
6.4.5.1 IntroductiontoJSON_TABLE.33
6.4.5.2 COLUMNSclausethatisnotnested.34
6.4.5.3 NestedCOLUMNSclause.36
6.4.5.4 PLAN clause.37
6.4.6 Conformancefeaturesforqueryoperators.41
6.5 ConstructorfunctionsandISJSONpredicate.43
6.5.1 TablesusedtoillustrateconstructorfunctionsandtheISJSONpredicate.43
6.5.2 JSON_OBJECT.44
6.5.3 JSON_OBJECTAGG.45
6.5.4 JSON_ARRAY.46
6.5.5 JSON_ARRAYAGG.47
6.5.6 ISJSONpredicate.49
6.5.7 HandlingofJSONnullsandSQLnulls.49
6.5.8 Conformancefeaturesforconstructorfunctions.50
7 SQL/JSONpathlanguage.51
7.1 OverviewofSQL/JSONpathlanguage.51
7.2 ObjectivesfortheSQL/JSONpathlanguage.52
7.3 Modes.53
7.3.1 Introductiontomodes.53
7.3.2 Exampleofstrictvslax.54
7.4 Lexicalissues.56
7.5 Syntaxsummary.58
7.6 Formalsemanticsandnotationalconventions.58
7.7 Primitiveoperationsinformalsemantics.59
7.7.1 Concatenation.59
7.7.2 unwrap().59
7.7.3 wrap().60
7.8 Mode declaration.60
7.9 .61
7.9.1 IntroductiontoJSONpathprimaries.61
7.9.2 Literals.61
7.9.3 Variables.62
iv ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
7.9.4 Parentheses.63
7.10 Accessors.63
7.10.1 Introductiontoaccessors.63
7.10.2 Member accessor.64
7.10.3 Memberwildcardaccessor.67
7.10.4 Element accessor.68
7.10.5 Elementwildcardaccessor.70
7.10.6 Sequencesemanticsoftheaccessors.71
7.11 Item methods.71
7.11.1 Introductiontoitemmethods.71
7.11.2 type().72
7.11.3 size().72
7.11.4 Numericitemmethods(double,ceiling,floor,abs).73
7.11.5 datetime().73
7.11.6 keyvalue().73
7.12 Arithmeticexpressions.75
7.12.1 Introductiontoarithmeticexpressions.75
7.12.2 Unaryplusandminus.75
7.12.3 Binaryoperations.76
7.13 Filterexpression.76
7.13.1 Introductiontofilterexpressions.76
7.13.2 true/falseandTrue/False.77
7.13.3 nullandUnknown.78
7.13.4 Errorhandlinginfilters.78
7.13.5 Truthtables.81
7.13.6 Comparisonpredicates.82
7.13.7 like_regexpredicate.83
7.13.8 starts withpredicate.84
7.13.9 exists predicate.84
7.13.10 is unknownpredicate.86
7.14 ConformancefeaturesforSQL/JSONpathlanguage.87
Bibliography.88
Index.89
©ISO/IEC2021–Allrightsreserved v

ISO/IEC19075-6:2021(E)
Tables
Table Page
1 JSON,SQL/JSON,andSQL(otherthanSQL/JSONvaluesandcounterparts).14
2 ParallelsbetweenJSONtextandSQL/JSONdatamodel.18
3 JSON_EXISTSsampledata.22
4 Resultofthesamplequery.23
5 Accessorexample.24
6 Result1.26
7 Result2.26
8 Result3.27
9 Result4.27
10 Result5.28
11 Result6.29
12 JSON_EXISTSresults.30
13 ONEMPTYresults.31
14 ARRAYWRAPPERresults.31
15 Illustratingdifferences.32
16 Comparisonofwrapperoptions.32
17 JSON_TABLEsampledatainabookrecommendationtable.33
18 Unnestedqueryresult.35
19 Nestedqueryresult.36
20 PLANqueryresult.39
21 SecondPLANqueryresult.39
22 ThirdPLANqueryresult.40
23 DEPTS table.43
24 JOBStable.43
25 EMPLOYEEStable.44
26 TheJSONobjectreturned.45
27 ReturnedJSONobjectwiththecorrespondingjobsequencenumber.46
28 ARRAYAGGqueryresult.48
29 SecondARRAYAGGqueryresult.48
30 ThirdARRAYAGGqueryresult.49
31 Threeaspectsofpathevaluationgovernedbymodes.54
32 Exampleofstrictvslax.55
33 FeaturesoftheSQL/JSONpathlanguage.58
34 Datausedbyunwrap()example.59
35 Datausedbywrap()example.60
36 ExamplesofatomicvaluesintheSQL/JSONpathlanguage.61
37 Examplesoftheescapingrules.61
38 Evaluationof'$.phones.type'inlaxmode.65
39 Intermediatestep.65
40 Evaluationof'$.phones[*].type'.66
41 Evaluationof'$.phones[*] ? (exists (@.type)).type'.66
42 Evaluationof'$.phones.*'inlaxmode.67
43 Evaluationof '$.phones[*].*'.67
44 Evaluationof'lax $.sensors.*[0, last, 2]'.69
45 Thestepintheevaluation.71
46 Resultofthequerywiththesampledata.74
47 Evaluationof 'lax -$.readings.floor()'.75
vi ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
48 Evaluationof'lax (-$.readings).floor()'.76
49 TableTwithtworows.78
50 ComputationonrowK=102.79
51 ModifiedtableT.79
52 ComputationonrowK=102inmodifiedtableT.79
53 Computationof'lax $ ? (@.hours > 9)'onrowK=102.80
54 Computationof'strict $ ? (@.hours > 9)'instrictmode.80
55 Resultof&&.81
56 Resultof||.81
57 Resultof!.81
58 Supportedcomparisons.82
59 Finalresult.83
60 AtablewithJSONcolumn.85
61 Evaluationof'strict $ ? (exists (@.name)).name'onrowK=201.85
62 Evaluationof'strict $ ? (exists (@.name)).name'onrowK=202.85
©ISO/IEC2021–Allrightsreserved vii

ISO/IEC19075-6:2021(E)
Figures
Figure Page
1 Relationshipsbetween“JSON”and“SQL/JSON”.14
2 TheSQL/JSONpathlanguagearchitecture.51
viii ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
Foreword
ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical
Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare
membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical
committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.
ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,
governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.
Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare
describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe
differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial
rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directivesorwww.iec.ch/mem-
bers_experts/refdocs).
Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof
patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details
ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or
ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent
declarationsreceived(seepatents.iec.ch).
Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot
constituteanendorsement.
Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions
relatedtoconformityassessment,aswellasinformationaboutISO’sadherencetotheWorldTrade
Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-
word.html.IntheIEC,seewww.iec.ch/understanding-standards.
ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-
mitteeSC32,Datamanagementandinterchange.
ThisfirsteditionofISO/IEC19075-6cancelsandreplacesISO/IECTR19075-6:2017.Severalsmall
technicalerrorshavebeencorrected,
ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsoftheISO/IEC
9075series:
— ISO/IEC9075-1,sixtheditionorlater;
— ISO/IEC9075-2,sixtheditionorlater;
— ISO/IEC9075-3,sixtheditionorlater;
— ISO/IEC9075-4,seventheditionorlater;
— ISO/IEC9075-9,fiftheditionorlater;
— ISO/IEC9075-10,fiftheditionorlater;
— ISO/IEC9075-11,fiftheditionorlater;
— ISO/IEC9075-13,fiftheditionorlater;
— ISO/IEC9075-14,sixtheditionorlater;
— ISO/IEC9075-15,secondeditionorlater;
— ISO/IEC9075-16,firsteditionorlater.
©ISO/IEC2021–Allrightsreserved ix

ISO/IEC19075-6:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
x ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“JavaScriptObjectNotation(JSON)”,introduceswhatisJSON.
5) Clause5,“TheSQL/JSONdatamodel”,introducesthedatamodelthatisusedbytheSQL/JSON
functionsandtheSQL/JSONpathlanguage.
6) Clause6,“SQL/JSONfunctions”,introducestheSQL/JSONfunctionstoqueryandconstructJSON.
7) Clause7,“SQL/JSONpathlanguage”,introducestheSQL/JSONpathlanguage.
©ISO/IEC2021–Allrightsreserved xi

ISO/IEC19075-6:2021(E)
xii ©ISO/IEC2021–Allrightsreserved

INTERNATIONAL STANDARD ISO/IEC 19075-6:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part6:
SupportforJSON
1 Scope
ThisdocumentdescribesthesupportinSQLforJavaScriptObjectNotation.
ThisdocumentdiscussesthefollowingfeaturesoftheSQLlanguage:
— StoringJSONdata.
— PublishingJSONdata.
— QueryingJSONdata.
— SQL/JSONdatamodelandpathlanguage.
©ISO/IEC2021–Allrightsreserved 1

ISO/IEC19075-6:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
2 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1andthefollowing
termsanddefinitionsapply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— IECElectropedia:availableathttp://www.electropedia.org/
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
3.1
JSONtext
sequenceoftokens
Note1toentry:JSONtextmustbeencodedinUnicode(UTF-8bydefault);insignificantwhitespacemay
beusedanywhereinJSONtextexceptwithinstrings(whereallwhitespaceissignificant),numbers,and
literals.JSONtextisasingleobjectorarray
3.2
token
strings,numbers,literals,oroneofsixstructuralcharacters
Note1toentry:Thesixstructuralcharactersare“{”,“}”,“[”,“]”,“:”,“,”
3.3
value
object,array,number,string,oroneofthreeliterals
3.4
type
primitivetypeorstructuredtype
3.5
primitivetype
string,number,Boolean,ornull
3.6
primitivevalue
valuethatisastring,number,Boolean,ornull
3.7
structuredtype
objectorarray
3.8
structuredvalue
valuethatisanobjectoranarray
©ISO/IEC2021–Allrightsreserved 3

ISO/IEC19075-6:2021(E)
3 Termsanddefinitions
3.9
string
sequenceofUnicodecharacters
Note1toentry:Somecharactersmustbe“escaped”byprecedingthemwithareversesolidus(“\”),while
anyorallcharacterscanberepresentedin“Unicodenotation”comprisingthestring'\u'followedby
fourhexadecimaldigitsortwosuchstringsrepresentingtheUTF-16surrogatepairsrepresentingchar-
actersnotontheBasicMultilingualPlane.
Note2toentry: Stringsaresurroundedbydouble-quotecharacters,whicharenotpartofthevalueof
thestrings.
3.10
number
sequencecomprisinganintegerpart,optionallyfollowedbyafractionalpartand/oranexponentpart
Note1toentry:Non-numericvalues,suchasinfinityandNaNarenotpermitted.
3.11
Boolean
literal“true”orliteral“false”
3.12
null
literal“null”
3.13
object
structurerepresentedby“{”,zeroormoremembersseparatedby“,”,and“}”
3.14
member
stringfollowedbyacolonfollowedbyavalueinanobject
Note1toentry:Amemberisalsoknownasa“name-valuepair”;thenameissometimescalleda“key”
andthesecondvalueissometimescalleda“boundvalue”.
3.15
array
structurerepresentedbya“[”,zeroormoreelementsseparatedby“,”,and“]”
3.16
element
valueinanarray
3.17
field
memberinanobject,elementinanarray,nameinamember,orvalueinamember
3.18
datamodel
definitionofwhatkindsofdatabelongtoaparticularuniverseofdiscourse,includingthe
operationsonthosekindsofdata
3.19
JSONdatamodel
(implicit)datamodelassociatedwithJSON
4 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
3 Termsanddefinitions
3.20
SQL/JSONdatamodel
datamodelcreatedforoperatingonJSONdatawithintheSQLlanguage
©ISO/IEC2021–Allrightsreserved 5

ISO/IEC19075-6:2021(E)
4 JavaScriptObjectNotation(JSON)
4.1 ContextforJSON
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1
andISO/IEC9075-2.
4.2 WhatisJSON?
JSON(anacronymfor“JavaScriptObjectNotation”)isbothanotation(thatis,asyntax)forrepresenting
dataandanimplieddatamodel.JSONisnotanobject-orienteddatamodel;thatis,itdoesnotdefinesets
ofclassesandmethods,typeinheritance,ordataabstraction.Instead,JSON“objects”aresimpledata
structures,includingarrays.RFC8259saysthatJSONisatextformatfortheserializationofstructured
data.Itsinitialintendedusewasasadatatransfersyntax.
ThecompletesyntaxofJSONisspecifiedinRFC8259.
Thefirst-classcomponentsoftheJSONdatamodelareJSONvalues.AJSONvalueisoneofthefollowing:
JSONobject,JSONarray,JSONstring,JSONnumber,oroneoftheJSONliterals:true,false,andnull.AJSON
objectiszeroormorename-valuepairsandisenclosedincurlybraces—{ }.AJSONarrayisanordered
sequenceofzeroormorevaluesandisenclosedinsquarebrackets—[ ].
HereisanexampleofaJSONobject:
{ "Name" : "Isaac Newton",
"Weight" : 80,
"Famous" : true,
"Phone" : null }
Thename-valuepairsareseparatedbycommas,andthenamesareseparatedfromthevaluesbycolons.
Thenamesarealwaysstringsandareenclosedin(double)quotationmarks.
HereisanexampleofaJSONarray:
[ "Robert J. Oppenheimer", 98, false, "Beechwood 45789" ]
InaJSONarray,thevaluesareseparatedbycommas.JSONarraysandobjectsarefullynestable.Thatis,
valuesinbothJSONobjectsandJSONarraysmaybeJSONstrings,JSONnumbers,JSONBooleans(repre-
sentedbytheJSONliteralstrueandfalse),JSONnulls(representedbytheJSONliteralnull),JSONobjects,
orJSONarrays.
JSONcanbeusedtorepresentassociativearrays—arrayswhoseelementsareaddressedbycontent,
notbyposition.AnassociativearraycanberepresentedinJSONasaJSONobjectwhosemembersare
name-valuepairs;thenameisusedasthe“index”intothe“array”—thatis,tolocatetheappropriate
memberintheobject—andthevalueisusedasthecontentoftheappropriatemember.Hereissuchan
associativearray:
{ "Isaac Newton" : "apple harvester",
"Robert J. Oppenheimer": "security risk",
6 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
4.2 WhatisJSON?
"Albert Einstein" : "patent clerk",
"Stephen Hawking" : "inspiration" }
AnextremelyimportantpartofJSON’sdesignisthatitisinherentlyschema-less.AnyJSONobjectcanbe
modifiedbyaddingnewname-valuepairs,evenwithnamesthatwereneverconsideredwhentheobject
wasinitiallycreatedordesigned.Similarly,anyJSONarraycanbemodifiedbychangingthenumberof
valuesinthearray.
4.3 RepresentationsofJSONdata
4.3.1 IntroductiontorepresentationsofJSONdata
Beforedelvingmuchdeeperintotheprimarytopicofthisdocument,readersshouldunderstandthat
JSONdatacanberepresentedinseveralwidely-acknowledgedand-usedforms.Themostobviousand
mosteasilyrecognizableisits“characterstring”representation,inwhichJSONdataisrepresentedin
Unicodecharactersasplaintext.Morespecifically,explicitcharacterssuchastheleftsquarebrace,
comma,rightcurlybrace,quotationmark,andlettersanddigitsareallusedintheirnativeUnicoderep-
resentation(UTF-8,UTF-16,UTF-32).
However,foravarietyofreasons,JSONdataissometimesstoredandexchangedinoneofseveralbinary
representations.Forexample,abinaryrepresentationofJSONdatamaybesignificantlysmaller(fewer
octets)thanthecharacterrepresentationofthesamedata,soareductioninnetworkbandwidthandor
storagemediacanbeachievedbytransferringorstoringthatdatainabinaryrepresentation.
Readersshouldnotethattherearenopublishedstandards,eitherfromtraditionaldejurestandards
organizationsnorfromconsortiaorotherdefactostandardsgroups,foranybinaryrepresentationsof
JSON.ThetwodescribedinthisdocumentarefrequentlyusedandmayberepresentativeofbinaryJSON
representationsgenerally.Thefollowingdiscussionisintendedonlytoillustratetheuseofandissues
withbinaryserializationsofJSON.TheSQLstandardleavesitimplementation-definedwhetherornot
suchrepresentationsaresupportedinanyparticularSQL-implementation.
4.3.2 Avro
Avroisdescribedasa“dataserializationsystem”.Assuch,itsuseisnotlimitedtoabinaryrepresentation
orasacompressionrepresentationofJSONdata.However,anumberofJSONenvironmentshavechosen
Avroastheirpreferredbinary,compressedrepresentation.
AvrohasanumberofimportantcharacteristicsthataffectitschoiceasaJSONrepresentation.
— Dataisrepresentedinavariable-length,“shortest”form;e.g.,,theinteger2andtheinteger2000
occupyadifferentnumberofoctetsinanAvrostring.
— Numbersarerepresentedusingaratherarcane“zig-zag”encoding(thisnotation“moves”thesign
bitfromitsnormalpositionasthefirstbittothelastbit;doingsopermitsremovingleadingzeros
fromthenumbers,thusmakingtheirrepresentationoccupyfeweroctets).
— Thereisnotaone-to-onemappingbetweenJSONatomictypesandAvroatomictypes.
— AvrodataisalwaysassociatedwithanAvroschema.AnAvroschemadescribesthephysicalstructure
ofthecorrespondingAvrodataandisneededmerelyto“unpack”Avrodatabecauseofthevariable-
lengthfieldsandthevariousencodingrules.AnAvroschemamayaccompanyeachindividualAvro
©ISO/IEC2021–Allrightsreserved 7

ISO/IEC19075-6:2021(E)
4.3 RepresentationsofJSONdata
datastring,oritmaybespecifiedseparatelyandappliedtoallAvrodatastringsin,say,anAvrodata
file.Avroschemastellalmostnothingaboutthedataotherthanhowitispackedintoadatastring.
— AvrostringscanbeencodedusingJSONnotation(whichsortofcontradictsitsuseasadifferent
representationforJSONdata)orusingabinarynotation.
ReadersshouldrecognizethatAvroisnotadifferentkindofdataatall.Itis,instead,merelyanotherway
ofrepresentingthesamedatathattheJSONcharacterstringformatrepresents.(Itshouldbenotedthat
notallpossibleAvrostringscanbetreatedasJSONdata;similarly,noteverycharacterstringisavalid
bitofJSONdata.)Inthisdocument,AvroisreferencedasonepossibleserializationofJSONdata;the
characterstringformatisanotherserializationofthesamedata.
4.3.3 BSON
BSON(variouslypronouncedasthoughitwerespelled“bison”oras“beeson”)isanotherdataserialization
system.BSONsaysthatitis“abinaryformatinwhichzeroormorekey/valuepairsarestoredasasingle
entity,”calleda“document”.BSONisusedby—andapparentlywascreatedfor—aspecificcommercial
product.
BSONstringsarenolessdifficultforhumanbeingstoreadthanAvrostringsare,butthedesignofBSON
issignificantlydifferentthanAvro’s.ABSONdocumentisroughlyasequenceofelements,eachofwhich
isintroducedbyasingle-octetcode(forexample,thehexadecimalvalue‘01’identifiestheelementasa
doubleprecisionfloatingpointvalue,whichisalwayseightoctetsinlength,and‘0D’identifiestheelement
asastringcontainingJavaScriptcode),followedbyanoptionalelementname,followedbythe(mandatory)
elementvalue.
BSON,likeAvro,isnotadifferentkindofdata,butmerelyprovidesyetanotherwayofrepresentingJSON
data.(AlsolikeAvro,notallBSONstringsrepresentvalidJSONdata.)
4.4 Schemas
4.4.1 JSONschemataandvalidity
NeitherRFC8259norISO/IEC22275:2018provideanymechanismbywhichJSONvaluescanbetested
forvalidityotherthanstrictsyntacticvalidity.
JSONtextissufficiently“self-describing”thatdataencodedinJSONiseasilyparsedandcanoftenbeused
inapplicationcomponentsthathavenospecificknowledgeofthedatacontainedtherein.Thislastfact
explainswhyJSONissosuccessfulinthebroaderdatamanagementcommunity,inspiteofthelackofa
standardwaytodocumentitsstructure.
ThereisatleastoneefforttodefineaschemaforJSONJSONschemathatwoulddescribethestructureof
JSONdatatobeconsidered“valid”forsomegivenapplication.However,theredoesnotappeartobeany
significantamountofinterestfromtheJSONcommunityfortherapiddevelopmentofaschemadefinition
languageforJSON.
WhileaschemalanguageforJSONdatacouldbeusefulinsomecircumstances,itdoesnotappearthat
suchalanguageiswidelyused,andISO/IEC9075-2neitherreliesonnorcreatessuchalanguage.
ISO/IEC9075-2usestheword“valid”todescribedatainstancesthatsatisfyallJSONsyntacticrequire-
ments.ISO/IEC9075-2specifiesanewSQLpredicate,ISJSON,totestthe(syntactic)validityofJSONdata
instances.
8 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
4.4 Schemas
4.4.2 Avroschemata
BecauseAvroisarepresentationinwhicheach“field”(abitofJSONdata)occupiesnomoreoctetsthan
isrequired,usingtheparticularencodingmethodfordataofeachtype,itisnotpossibletosimplyindex
toaspecificfieldineachAvrostring.Infact,becauseofthewaythatAvroencodesitsfields,itisnot
possibletoscananAvrostringandidentifythestartofthesecond,third,ortwenty-fifthfieldinthat
string.
Consequently,AvrospecifiesthateachAvrovaluebedescribedbyanAvroschema.Avroschemataare
expressedinthecharacterrepresentationofJSON.TheschemathatrepresentsanentireAvrostringis
composedof“smaller”schematathatrepresenteachfieldintheAvrostring.Theschemadescribeseach
fieldbyitsname,datatype,and(ifnotalreadyunambiguous)length.Thus,anapplicationwishingto
accessthefieldsinanAvrostringmustfirstparsetheschemaofthatstring,thenusethatinformation
tolocatethedesiredfieldsinthestringandto“decode”thefieldcontentsintoavalueofaJSONdatatype.
BecauseeachJSONtextcanbeofadifferentsizeorcontaindifferentcomponents,onemightwishto
provideadifferentschemaforeachJSONtext.aschemathatuniquelydescribesthattextandnot(nec-
essarily)anyotherJSONtext.ThisapproachnecessarilycreatesanincreaseinsizeoftheJSONtexts
associatedwiththoseschemata.ForsmallJSONtextsand/orJSONtextswithagreatmanyfields,the
overhead(inoctets)ofprovidingaschemaforeachsuchtextintheAvrorepresentationbecomesunac-
ceptableveryquickly.ForverylargeJSONtexts,particularlywithsmallnumbersof(verylarge)fields,
thepresenceofaper-textschemamaybeperfectlyreasonable.
AvrodoesnotrequirethataschemabeprovidedalongwitheachJSONtext,individually.Itdoespermit
thatapproach,butitalsoallowsforasingleschematodescribealloftheAvro-representedJSONtexts
ina“containerfile.”Aslongasallofthetextsinthatcontainerfilearesufficientlyalike,asingleschema
isadequate—and,ofcourse,producesmuch-reducedoverhead.Inthecontextofthisdocument,an
entirecolumncontainingtheAvrorepresentationofJSONtextsactsasthe“containerfile”.TheAvro
schemaassociatedwithsuchacolumnispartofthemetadatadescribingthecolumn.
Intheprecedingparagraph,thephrase“sufficientlyalike”wasused.Thatphrasemeansthateach
object/arrayintherowsofacolumncontainthesamenumberofmembers/elements,eachhavingthe
samename(forobjects)anddatatype.Italsomeansthatthelengths(numberofoctets)ofeachmem-
ber/elementmustberespectivelythesame,andthatisnotalwayseasytoensure.
NOTE1—Therefore,theJSONobjects“{"name":"Joe","salary":85000}”and“{"name":"Bob","salary":78000}”
are“sufficientlyalike”,but“{"name":"Ann","bonus":85000}”isnot.
4.4.3 BSONschemata
BecausetheBSONrepresentationofJSONcontainsaone-octetcodeasthefirstoctetofeveryfield,itis
possibletoscanaBSONvalueanduniquelyidentifyeachfieldanditsdatatype.Consequently,nosort
ofschemaisrequiredforBSON-representedJSONdata.
However,BSON—likeAvro—usesvariable-lengthfields,sothatcorrespondingmembers/elementsin
differentobjects/arrayscanhavesignificantlydifferentlengths.ScanningBSONstringstolocatethose
fieldcodes(andthusthefieldsthemselves)requiresCPUcycles.BSONmightbenefitfromhavingaschema
capabilitysomewhatsimilartoAvro’s,butitiscertainlynotnecessary.
©ISO/IEC2021–Allrightsreserved 9

ISO/IEC19075-6:2021(E)
4.5 WhydoesJSONmatterinthecontextofSQL?WhatisJSON’srelationshiptoNoSQL?
4.5 WhydoesJSONmatterinthecontextofSQL?WhatisJSON’srelationship
toNoSQL?
ItisunclearthatJSONandSQLISO/IEC9075-2haveanyinherentrelationship,butitisequallyclearthat
thetechnical,business,andgovernmentworldsareincreasinglyusingbothkindsofdataintheirenviron-
ments.Individualapplicationsarerequiredtoaccessandmanipulateallofthesekindsofdata,often
concurrently.Therearegreatbenefitswhenasingledatamanagementsystemcanconcurrentlyhandle
allofthedata.Amongthebenefitsare:reducedadministrativecosts,improvedsecurityandtransaction
management,betterperformanceandgreaterpossibilitiesforoptimization,betterresourceallocation,
andincreaseddeveloperproductivity.
IncorporationofJSONintotheSQLumbrellaoffersimplementersandusersalikethebenefitsdescribed
above.ThatfacteasilyjustifiestherelativelysmallincreaseinsizeandcomplexityoftheSQLstandard,
especiallywhentheapproachdescribedinthisdocumentisused.
NoSQLdatabasesystemsNoSQLDBaregenerallycharacterizedbythefollowingattributes:
— Theydonotusetherelationalmodel(theyalsodonotuseanumberofotherdatamodels).
— Theytendtobefocusedon“bigdata”andonapplicationsforwhich“approximate”answersareoften
adequate.
— Theyareoptimizedfordataretrieval,notfordatacreationorupdate,norontherelationshipsbetween
data.
— TheyusuallydonotuseACIDDistributedTransactionProcessingtransactions;instead,theymay
offertransactionalmodelsthatresultin“eventualconsistency”.
— Theytendtobedesignedusingdistributed,fault-tolerant,highlyparallelhardwareandsoftware
architectures.
NoSQLdatabasesystemscomeinaverywidevarietyofkinds,basedontheirtargetedmarketplaces,
datamodels,andapplicationrequirements.Theyhavebeencrudelytaxonomizedinto:
— Key-valuestores
— “BigTable”stores
— Documentstores
— Graphdatabases
Key-valuestoresprovideexactlythecapabilitythatthenameimplies:applicationsprovideakeyandare
givenavalueinreturn.Key-valuestoresmaymanageonly“flat”pairs,ortheymaymanagehierarchical
pairs.
BigTablestoresimplementmulti-dimensionalarrayssuchthatapplicationsprovideoneormoreindex
values(oftenstringsusedaskeyvalues,insteadofnumericindexes)thattogetherprovidethelocation
ofacell,thevalueofwhichisreturnedtotheapplication.
Documentstores,contrarytowhatthenamemanysuggesttomany,donotnecessarilystoretextual
documentssuchasbooks,specifications,ormagazines;instead,theystoredatathatmaybetraditional
textualdocumentsororganizedcollectionsofstructured(andsemi-structured)data.
Graphdatabasesprovideawaytostoredatathatisgenerallylinkedtogetherintographs(oftendirected
graphs,sometimestreesinparticular).
Many,buthardlyall,NoSQLdatabasesystemsmanagedatarepresentedinJSON,especiallykey-value
storesanddocumentstores.
10 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-6:2021(E)
4.5 WhydoesJSONmatterinthecontextofSQL?WhatisJSON’srelationshiptoNoSQL?
TherapidlyincreasinguseofJSONtointerchangedatabetweenWebapplicationshascaughttheattention
ofacademics,technologists,developers,andevenenterprisemanagement.SQLdatabaseimplementers
areincreasinglyconvincedthattheymustsupportJSONdata“natively”.Thisdocumentdescribesthe
approachtakeninISO/IEC9075-2,whichallowssuchimplementerstoprovidethatsupportinacommon
manner.
4.6 UsecasesforJSONsupportinSQL
4.6.1 Introductiontotheusecases
Therearethreeprimaryusecases:
— JSONdataingestionandstorage
— JSONdatagenerationfromrelationaldata
— QueryingJSONaspersistentsemi-structureddatamodelinstances
Thefollowingsectionsdiscusstheseusecasesingreaterdetail.
4.6.2 JSONdataingestionandstorage
DefininganewnativedatatypeisbothcostlytoimplementforSQL-implementationsandcostlytoadopt
bydatabasetoolsandapplicationsalike.Thus,ISO/IEC9075-2tooktheapproachtoingestJSONdataas
characterstringsorbinarystringsthatarethenstoredinordinarySQLcolumnsofsomeexistingstring
type.WhensuchdataisretrievedfromthosecolumnsforuseinJSON-basedfunctions,itistransformed
(parsed)intoinstancesofaninternalSQL/JSONdatamodelthatisneverdirectlyexposedtotheapplication
author.
4.6.3 JSONdatagenerationfromrelationaldata
Thisusecaseasks“HowcanJSONdatainstancesbe(declaratively)generatedfromrelationaltablesfor
dataexport?”ApplicationsthatarebasedonJSONdatanotonlywanttostoreandretrievesuchdata
upondemand,buttheytypicallywanttheirqueriesagainstsuchdatatoprovideresultsinthesameform
—JSON.Althoughitistrivialtoprovideproceduralmechanismsbywhichapplicationscanlaboriously
(andwithmanylikelyerrors)constructJSONdata,SQL’sdeclarativenaturesuggeststhatJSONobjects
andarraysshouldbegeneratedinsteadofpotentiallylengthycharacterstringsthatrepresentsuchobjects
andarrays.(Readersarecautionednottomisinterpretthisusecaseasrequiringprovisionofa“bulk
JSONdataexport”facility.)
ISO/IEC9075-2addressesthisusecasebyprovidingseveralfunctionsthattransformthedatastoredin
SQLtablesintoinstancesoftheinternalSQL/JSONdatamodelthatcan,ifneeded,beserializedbackinto
characterstringform.Thisdocumentprovidesseveralexamplesforthosefunctions.
©ISO/IEC2021–Allrightsreserved 11

ISO/IEC19075-6:2021(E)
4.6 UsecasesforJSONsupportinSQL
4.6.4 QueryingJSONaspersistentsemi-structureddatamodelinstances
ThisusecaseexploreshowJSONdatathatisstoreddirectlyinSQLtablescanbequeried.Directmapping
ofentireSQLtablesintosingle(or,necessarily,multiple)JSONobjectsorarrayshasnotbeenspecified
inISO/IEC9075-2,althoughsupportisprovidedforsuchmappingswhenneededbyapplications.Instead,
JSONdataisstoredwithinanopaquedatatype(specificallyanSQLstringorLargeObject)thatcanbe
manipulatedthroughthefunctionalinterfacespecifiedinISO/IEC9075-2,asillustratedbyexamplesin
thisdocument.
4.7 Whatfeaturesaddressthoseusecases?
4.7.1 Addressingtheusecases
Theusecasesareaddressedasfollows:
— SQLqueryexpressionscanaccessJSONdataaccordingtoitsstructure(e.g.,,usingthenamesofkey-
valuepairsinJSONobjects,positionsinJSONarrays,etc.).
— SQLqueriescangenerateJSONdatadirectlyforreturntoinvokersofthosequeries.
— SQLtablescanstoreJSONdatapersistently.
Inthenextsections,eachofthese“macro-features”areexploredinturn.
4.7.2 StoringJSONdatainanSQLtable
TheapproachtakenbyISO/IEC9075-2anddescribedinthisdocumentistostoreJSONdataintocharacter
stringcolumnsorbinarystringcolumnsthataredefinedwithinordinarySQLtables.Thatpermitsthose
JSONdatatoparticipateinSQLqueries(and,importantly,SQL-transactions)inthesamemannerasthe
datastoredinothercolumnsofthesametables.Bychoosingtousecolumnswhosedeclaredtypesare
stringtypes,thestandardization(andimplementation)overheadofcreatinganewbuilt-inSQLdatatype
isavoidedwithoutlosinganysignificantadvantagesofabuilt-intype.
Applications,however,arenotexpectedtoprovidedetailedcodetomanipulateJSONdatainthosestrings
directlyintheformofcharacterstringoperations.ISO/IEC9075-2providesanumberofbuilt-inSQL
functionsthataccess(query)JSONdatastoredinsuchcolumns.ThesefunctionsaredescribedinClause6,
“SQL/JSONfunctions”.
4.7.3 GeneratingJSONinanSQLquery
ISO/IEC9075-2providesbuilt-infunctionsthatgenerateJSONobjectsandJSONarraysastheresultsof
SQLqueries,whetherthesourceofthedataqueriedisJSONdataorordinarySQLdata.Thesefunctions
arealsodescribedinClause6,“SQL/JSONfunctions”.
12 ©ISO/IEC2021–Allrightsreserved
...