DM32.2-2014-00025r1-sql-json-part-2.pdf

213654

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 1 of 162 -

Title: SQL/JSON part 2 - Querying JSON

Author: Fred Zemke, Beda Hammerschmidt, Krishna Kulkarni, Zhen Hua Liu, Doug McMahon, Jim Melton, Jan-Eike Michels, Fatma Özcan, Hamid Pirahesh

Source: U.S.A.

Status: Change proposal

Date: March 4, 2014

Abstract

This paper addresses Comment #257, P02-USA-950, by proposing facilities for querying JSON using SQL.

References

[SQL-92] Database language SQL", ANSI X3.135-1992 [Foundation:1999] Jim Melton (ed), "ISO International Standard (IS) Database Language SQL - Part 2: SQL/Foundation", ISO/IEC 9075-2:1999 [Foundation 7CD1] Jim Melton (ed), "Committee Draft (CD) Database Language SQL - Part 2: SQL/Foundation", ISO/IEC JTC1/SC32 WG3:USN-003 = ANSI INCITS

DM32.2-2012-0Onnn

[Foundation 7IWD4] Jim Melton (ed), "Informal Working Draft (IWD) Database Language SQL - Part 2: SQL/Foundation", ISO/IEC JTC1/SC32 WG3:PEK-003 = ANSI

INCITS DM32.2-2013-200

[SQL/XML:2011] Jim Melton (ed), "ISO International Standard (IS) Database Language SQL - Part 14: XML-Related Specifications (SQL/XML)", ISO/IEC 9075-

14:2011

[XQuery 1.0.2] "XQuery 1.0: An XML Query Language (Second Edition)", http:// www.w3.org/TR/xquery/ [SQL/JSON part 1] Jim Melton, "SQL/JSON part 1", ANSI INCITS DM32.2-2014-00024 =

ISO/IEC JTC1/SC32 WG3:PEK-nnn

[RFC 2119] Internet Engineering Task Force, Key words for use in RFCs to Indicate

Requirement Levels, RFC 2119

https://www.ietf.org/rfc/rfc2119.txt [RFC 4627] Internet Engineering Task Force, The application/json Media Type for Jav- aScript Object Notation (JSON), RFC 4627 http://tools.ietf.org/html/rfc4627

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 2 of 162 -

SQL/JSON part 2 - Querying JSON

[ECMA-262 5.1] "ECMAscript language specification 5.1", June 2011, http://www.ecma- international.org/publications/files/ECMA-ST/Ecma-262.pdf [JSON.org] http://www.json.org/ [JPath] http://projects.plural.cc/projects/jsonij/wiki/JPath [JSONPath] http://goessner.net/articles/JsonPath/ [JAQL] http://code.google.com/p/jaql/wiki/JaqlOverview [JSONiq] http://www.jsoniq.org/ [Mongo] http://docs.mongodb.org/manual/ [AVRO] http://avro.apache.org/ [AVRO spec] http://avro.apache.org/docs/current/spec.html [BSON spec] http://bsonspec.org/#/specification

1. Introduction

This paper addresses Comment #257, P02-USA-950, which says

1-Major Technical

P02-No specific location

JavaScript Object Notation (popularly known as JSON) [RFC4627] is becoming increasingly important in Web- and Cloud-based communities for exchanging data between Web-based applications. This has in turn led to the demands for storage, retrieval, querying, and manipulation of JSON data in the context of SQL It is important that SQL respond to these requirements by providing appropriate extensions for handling JSON data. The companion paper [SQL/JSON part 1] provides an introduction to JSON and proposes SQL operators to construct JSON values stored in either character or binary strings. This paper pro- vides support for querying JSON data in SQL. The discussion sections in this paper are organized as follows:

1.Section 2. "Overview of the API" on page 3.

2.Section 3. "Formal discussion of the API" on page 26

3.Section 4. "Data model" on page 39

4.Section 5. "Path language" on page 43

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 3 of 162 -

SQL/JSON part 2 - Querying JSON

2. Overview of the API

This section presents an overview of the proposed features for querying JSON using SQL. This is an informal treatment, in which syntax and concepts are introduced as they are encountered, with- out attempting to be complete and thorough. After the overview, we will provide a formal treat- ment of all proposed features. We propose five operators to query JSON using SQL: IS JSON - predicate to test that a string contains JSON JSON_VALUE - extract an SQL value from a JSON value JSON_TABLE - convert a JSON value to an SQL table JSON_EXISTS - predicate to determine if a JSON value contains some information JSON_QUERY - extract a JSON value from a JSON value

2.1 Validation - IS JSON predicate

The first step in handling JSON will frequently be to validate that a string (character or binary) conforms to the JSON specification [RFC 4627]; this is satisfied by the IS JSON predicate, exem- plified:

WHERE T.C IS JSON

In general terms, this predicate is True

if T.C is a string (character or binary) that conforms to [RFC 4627]. The encoding is deduced from the data (from the character set or from the first four bytes of the binary string).

There are two important issues to understand:

- unique key names - SQL null values

2.1.1 Unique key names

The first issue is that [RFC 4627] section 2.2 "Objects" says

The names within an object SHOULD be unique.

The all-caps word SHOULD is defined in [RFC 2119]

3. SHOULD This word, or the adjective "RECOMMENDED", mean that there

may exist valid reasons in particular circumstances to ignore a particular item, but the full implications must be understood and carefully weighed before choosing a different course Thus it is recommended that JSON objects should have unique key name, but it is not required. We decided that there may exist JSON texts that do not enforce this uniqueness constraint, and

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 4 of 162 -

SQL/JSON part 2 - Querying JSON

also that users may be interested in enforcing the uniqueness constraint. Therefore we propose two options on the IS JSON predicate, to either enforce the constraint or not. The two choices are illustrated as follows:

T.C IS JSON WITH UNIQUE KEYS

T.C IS JSON WITHOUT UNIQUE KEYS

Since enforcing a constraint is costly, the default is not to check, that is, T.C IS JSON is equivalent

to T.C IS JSON WITHOUT UNIQUE KEYS.

2.1.2 SQL null values

The other issue is how to handle a null input to the IS JSON predicate, such as

CAST (NULL AS CLOB) IS JSON

A null value is not a string conforming to [RFC 4627], so one might think that the result should be False . However, there is a long precedent in SQL that most predicates return Unknown on null input, and we propose that behavior for the IS JSON predicate.

There are two important use cases to consider:

- table constraint to insure that only conformant JSON is inserted in a column - WHERE clause to filter rows to insure that only conformant JSON is selected Constraints: a user may wish to insure that a column contains only JSON. The most natural approach seems to be

CREATE TABLE T (

D INTEGER CHECK (D > 0),

J CLOB CHECK (J IS JSON)

Question: What does the user do if the user wishes to insert a row and currently knows the value

of D but does not know the value of J? The usual approach to this situation is to insert a null value

as a place holder, indicating that the correct value is not currently known, but may be supplied later. So the user performs

INSERT INTO T (D, J) VALUES (1, NULL)

This operation will succeed, because the check constraint will evaluate to Unknown , which is suf- ficient; only False rows are rejected by a check constraint. The check constraint on column D is analogous; it requires a positive integer. Consider this inser- tion:

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 1 of 162 -

Title: SQL/JSON part 2 - Querying JSON

Author: Fred Zemke, Beda Hammerschmidt, Krishna Kulkarni, Zhen Hua Liu, Doug McMahon, Jim Melton, Jan-Eike Michels, Fatma Özcan, Hamid Pirahesh

Source: U.S.A.

Status: Change proposal

Date: March 4, 2014

Abstract

This paper addresses Comment #257, P02-USA-950, by proposing facilities for querying JSON using SQL.

References

[SQL-92] Database language SQL", ANSI X3.135-1992 [Foundation:1999] Jim Melton (ed), "ISO International Standard (IS) Database Language SQL - Part 2: SQL/Foundation", ISO/IEC 9075-2:1999 [Foundation 7CD1] Jim Melton (ed), "Committee Draft (CD) Database Language SQL - Part 2: SQL/Foundation", ISO/IEC JTC1/SC32 WG3:USN-003 = ANSI INCITS

DM32.2-2012-0Onnn

[Foundation 7IWD4] Jim Melton (ed), "Informal Working Draft (IWD) Database Language SQL - Part 2: SQL/Foundation", ISO/IEC JTC1/SC32 WG3:PEK-003 = ANSI

INCITS DM32.2-2013-200

[SQL/XML:2011] Jim Melton (ed), "ISO International Standard (IS) Database Language SQL - Part 14: XML-Related Specifications (SQL/XML)", ISO/IEC 9075-

14:2011

[XQuery 1.0.2] "XQuery 1.0: An XML Query Language (Second Edition)", http:// www.w3.org/TR/xquery/ [SQL/JSON part 1] Jim Melton, "SQL/JSON part 1", ANSI INCITS DM32.2-2014-00024 =

ISO/IEC JTC1/SC32 WG3:PEK-nnn

[RFC 2119] Internet Engineering Task Force, Key words for use in RFCs to Indicate

Requirement Levels, RFC 2119

https://www.ietf.org/rfc/rfc2119.txt [RFC 4627] Internet Engineering Task Force, The application/json Media Type for Jav- aScript Object Notation (JSON), RFC 4627 http://tools.ietf.org/html/rfc4627

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 2 of 162 -

SQL/JSON part 2 - Querying JSON

[ECMA-262 5.1] "ECMAscript language specification 5.1", June 2011, http://www.ecma- international.org/publications/files/ECMA-ST/Ecma-262.pdf [JSON.org] http://www.json.org/ [JPath] http://projects.plural.cc/projects/jsonij/wiki/JPath [JSONPath] http://goessner.net/articles/JsonPath/ [JAQL] http://code.google.com/p/jaql/wiki/JaqlOverview [JSONiq] http://www.jsoniq.org/ [Mongo] http://docs.mongodb.org/manual/ [AVRO] http://avro.apache.org/ [AVRO spec] http://avro.apache.org/docs/current/spec.html [BSON spec] http://bsonspec.org/#/specification

1. Introduction

This paper addresses Comment #257, P02-USA-950, which says

1-Major Technical

P02-No specific location

JavaScript Object Notation (popularly known as JSON) [RFC4627] is becoming increasingly important in Web- and Cloud-based communities for exchanging data between Web-based applications. This has in turn led to the demands for storage, retrieval, querying, and manipulation of JSON data in the context of SQL It is important that SQL respond to these requirements by providing appropriate extensions for handling JSON data. The companion paper [SQL/JSON part 1] provides an introduction to JSON and proposes SQL operators to construct JSON values stored in either character or binary strings. This paper pro- vides support for querying JSON data in SQL. The discussion sections in this paper are organized as follows:

1.Section 2. "Overview of the API" on page 3.

2.Section 3. "Formal discussion of the API" on page 26

3.Section 4. "Data model" on page 39

4.Section 5. "Path language" on page 43

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 3 of 162 -

SQL/JSON part 2 - Querying JSON

2. Overview of the API

This section presents an overview of the proposed features for querying JSON using SQL. This is an informal treatment, in which syntax and concepts are introduced as they are encountered, with- out attempting to be complete and thorough. After the overview, we will provide a formal treat- ment of all proposed features. We propose five operators to query JSON using SQL: IS JSON - predicate to test that a string contains JSON JSON_VALUE - extract an SQL value from a JSON value JSON_TABLE - convert a JSON value to an SQL table JSON_EXISTS - predicate to determine if a JSON value contains some information JSON_QUERY - extract a JSON value from a JSON value

2.1 Validation - IS JSON predicate

The first step in handling JSON will frequently be to validate that a string (character or binary) conforms to the JSON specification [RFC 4627]; this is satisfied by the IS JSON predicate, exem- plified:

WHERE T.C IS JSON

In general terms, this predicate is True

if T.C is a string (character or binary) that conforms to [RFC 4627]. The encoding is deduced from the data (from the character set or from the first four bytes of the binary string).

There are two important issues to understand:

- unique key names - SQL null values

2.1.1 Unique key names

The first issue is that [RFC 4627] section 2.2 "Objects" says

The names within an object SHOULD be unique.

The all-caps word SHOULD is defined in [RFC 2119]

3. SHOULD This word, or the adjective "RECOMMENDED", mean that there

may exist valid reasons in particular circumstances to ignore a particular item, but the full implications must be understood and carefully weighed before choosing a different course Thus it is recommended that JSON objects should have unique key name, but it is not required. We decided that there may exist JSON texts that do not enforce this uniqueness constraint, and

ISO/IEC JTC1/SC32 WG3:PEK-nnn

ANSI INCITS DM32.2-2014-00025r1

- 4 of 162 -

SQL/JSON part 2 - Querying JSON

also that users may be interested in enforcing the uniqueness constraint. Therefore we propose two options on the IS JSON predicate, to either enforce the constraint or not. The two choices are illustrated as follows:

T.C IS JSON WITH UNIQUE KEYS

T.C IS JSON WITHOUT UNIQUE KEYS

Since enforcing a constraint is costly, the default is not to check, that is, T.C IS JSON is equivalent

to T.C IS JSON WITHOUT UNIQUE KEYS.

2.1.2 SQL null values

The other issue is how to handle a null input to the IS JSON predicate, such as

CAST (NULL AS CLOB) IS JSON

A null value is not a string conforming to [RFC 4627], so one might think that the result should be False . However, there is a long precedent in SQL that most predicates return Unknown on null input, and we propose that behavior for the IS JSON predicate.

There are two important use cases to consider:

- table constraint to insure that only conformant JSON is inserted in a column - WHERE clause to filter rows to insure that only conformant JSON is selected Constraints: a user may wish to insure that a column contains only JSON. The most natural approach seems to be

CREATE TABLE T (

D INTEGER CHECK (D > 0),

J CLOB CHECK (J IS JSON)

Question: What does the user do if the user wishes to insert a row and currently knows the value

of D but does not know the value of J? The usual approach to this situation is to insert a null value

as a place holder, indicating that the correct value is not currently known, but may be supplied later. So the user performs

INSERT INTO T (D, J) VALUES (1, NULL)

This operation will succeed, because the check constraint will evaluate to Unknown , which is suf- ficient; only False rows are rejected by a check constraint. The check constraint on column D is analogous; it requires a positive integer. Consider this inser- tion: