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 I'information - Recommandations pour I'utilisation du langage de base de données SQL Partie 6: Support pour JSON Reference number TEC IS0/IEC 19075-6:2021(E) tso @IS0/IEC 2021 IS0/IEC 19075-6:2021(E) COPYRIGHT PROTECTED DOCUMENT IS0/IEC2021 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:+4122 749 0111 Email: [email protected] Website:www.iso.org Published in Switzerland i @ IS0/IEC 2021 - All rights reserved IS0/IEC 19075-6:2021(E) Contents Page Foreword.. .ix Introduction. xi 1 Scope. 2 Normative references. 3 Terms and definitions. 4 JavaScript Object Notation (JSON). 4.1 Context for JSON. 4.2 What is JSON?. 4.3 Representations of JSON data. 4.3.1 IntroductiontorepresentationsofJsoNdata. 4.3.2 Avro. 4.3.3 BSON. 4.4 Schemas. 4.4.1 JSON schemata and validity. 4.4.2 Avroschemata 4.4.3 BSON schemata. 4.5 Why does JSON matter in the context of SQL? What is JSON's relationship to NoSQL? 10 4.6 Use cases for JSON support in SQL. 4.6.1 Introduction to the use cases. 4.6.2 JSON data ingestion and storage. 4.6.3 JSON data generation from relational data. 4.6.4 Querying JsoN as persistent semi-structured data model instances. 4.7 What features address those use cases?. . 4.7.1 Addressing the use cases. 4.7.2 Storing JSON data in an SQL table. . 12 4.7.3 Generating JSON in an SQL query. . 12 4.7.4 Querying JSON data in SQL tables using SQL. 13 5 The SQL/JSON data model........ 14 5.1 Introduction to the SQL/JSON data model. . 5.2 SQL/JSON items.. 5.2.1 Definition of SQL/JSON items. 15 5.2.2 Atomic values. 5.2.3 SQL/JSON arrays. 5.2.4 SQL/JSON objects. 5.3 SQL/JSON sequences 5.4 Parsing JSON. 5.5 Serializing JSON. 18 6 SQL/JSON functions. 19 @ IS0/IEC 2021 - All rights reserved ii IS0/IEC 19075-6:2021(E) 6.1 Introduction to SQL/JSON functions. 19 6.2 Handle JSON using built-in functions. 19 6.3 JSON API common syntax. 19 6.3.1 Introduction to JSON API common syntax. 19 6.3.2 JSON value expression. .20 6.3.3 Path expression. .20 6.3.4 PASSING clause. 6.3.5 JSON output clause. . 6.3.6 ON ERROR and ON EMPTY syntax. 6.4 Query functions. .22 6.4.1 The four query functions. 22 6.4.2 JSON_EXISTS. 6.4.3 JSON_VALUE. 25 6.4.4 JSON_QUERY. 6.4.5 JSON_TABLE. 33 6.4.5.1 Introduction to JSON_TABLE. 6.4.5.2 COLUMNS clause that is not nested. 34 6.4.5.3 Nested COLUMNS clause. . 36 6.4.5.4 PLAN clause.. 6.4.6 Conformance features for query operators. 6.5 Constructor functions and IS JSON predicate. 43 6.5.1 Tables used to illustrate constructor functions and the IS JSON predicate 43 6.5.2 JSON_OBJECT. 6.5.3 JSON_OBJECTAGG. 45 6.5.4 JSON_ARRAY. 46 6.5.5 JSON_ARRAYAGG. 6.5.6 IS JSON predicate. . 6.5.7 Handling of JSON nulls and SQL nulls. 6.5.8 Conformance features for constructor functions. . ..50 7 SQL/JSON path language. .. . 7.1 Overview of SQL/JSON path language. .51 7.2 Objectives for the SQL/JSON path language. .52 7.3 Modes.. 7.3.1 Introduction to modes. 53 7.3.2 Example of strict vs lax. . 54 7.4 Lexical issues. .. 56 7.5 Syntax summary.. 58 7.6 Formal semantics and notational conventions. 58 7.7 Primitive operations in formal semantics. 59 7.7.1 Concatenation. . 59 7.7.2 unwrapO.. . 59 7.7.3 60 7.8 Mode declaration. 60 7.9 <JSON path primary>. 7.9.1 Introduction to JSON path primaries. 7.9.2 Literals. 7.9.3 Variables. iv @ IS0/IEC 2021 - All rights reserved IS0/IEC 19075-6:2021(E) 7.9.4 Parentheses. 63