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
Current Stage
6060 - International Standard published
Start Date
01-Sep-2021
Completion Date
01-Sep-2021
Ref Project

RELATIONS

Buy Standard

Standard
ISO/IEC 19075-6:2021 - Information technology -- Guidance for the use of database language SQL
English language
90 pages
sale 15% off
Preview
sale 15% off
Preview
Draft
ISO/IEC PRF 19075-6:Version 10-jul-2021 - Information technology -- Guidance for the use of database language SQL
English language
90 pages
sale 15% off
Preview
sale 15% off
Preview

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.