ISO/IEC 19075-6:2021
(Main)Information technology — Guidance for the use of database language SQL — Part 6: Support for JSON
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
Relations
Buy Standard
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 19075-6:2021(E)
©
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-6:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
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
---------------------- Page: 7 ----------------------
ISO/IEC19075-6:2021(E)
Figures
Figure Page
1 Relationshipsbetween“JSON”and“SQL/JSON”.14
2 TheSQL/JSONpathlanguagearchitecture.51
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
ISO/IEC19075-6:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
x ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
ISO/IEC19075-6:2021(E)
xii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
ISO/IEC19075-6:2021(E)
3 Termsanddefinitions
3.20
SQL/JSONdatamodel
datamodelcreatedforoperatingonJSONdatawithintheSQLlanguage
©ISO/IEC2021–Allrightsreserved 5
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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,soareduction
...
INTERNATIONAL ISO/IEC
STANDARD 19075-6
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 6:
Support for JSON
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-6:2021(E)
©
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-6:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
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
---------------------- Page: 7 ----------------------
ISO/IEC19075-6:2021(E)
Figures
Figure Page
1 Relationshipsbetween“JSON”and“SQL/JSON”.14
2 TheSQL/JSONpathlanguagearchitecture.51
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
ISO/IEC19075-6:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
x ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
ISO/IEC19075-6:2021(E)
xii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
ISO/IEC19075-6:2021(E)
3 Termsanddefinitions
3.20
SQL/JSONdatamodel
datamodelcreatedforoperatingonJSONdatawithintheSQLlanguage
©ISO/IEC2021–Allrightsreserved 5
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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.
Readersshouldnotethatt
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.