JSON functions in GoogleSQL
GoogleSQL for Spanner supports the following functions, which can retrieve andtransform JSON data.
Categories
The JSON functions are grouped into the following categories based on theirbehavior:
| Category | Functions | Description |
| Extractors | JSON_QUERYJSON_VALUEJSON_QUERY_ARRAYJSON_VALUE_ARRAY | Functions that extract JSON data. |
| Lax converters | LAX_BOOLLAX_FLOAT64LAX_INT64LAX_STRING | Functions that flexibly convert a JSON value to a SQL value without returning errors. |
| Converters | BOOLBOOL_ARRAYFLOAT64FLOAT64_ARRAYFLOAT32FLOAT32_ARRAYINT64INT64_ARRAYSTRINGSTRING_ARRAY | Functions that convert a JSON value to a SQL value. |
| Other converters | PARSE_JSONTO_JSONSAFE_TO_JSONTO_JSON_STRING | Other conversion functions from or to JSON. |
| Constructors | JSON_ARRAYJSON_OBJECT | Functions that create JSON. |
| Mutators | JSON_ARRAY_APPENDJSON_ARRAY_INSERTJSON_REMOVEJSON_SETJSON_STRIP_NULLS | Functions that mutate existing JSON. |
| Accessors | JSON_KEYSJSON_TYPE | Functions that provide access to JSON properties. |
| Predicates | JSON_CONTAINS | Functions that returnBOOL when checking JSON documents for certain properties. |
Function list
| Name | Summary |
|---|---|
BOOL | Converts a JSON boolean to a SQLBOOL value. |
BOOL_ARRAY | Converts a JSON array of booleans to a SQLARRAY<BOOL> value. |
FLOAT64 | Converts a JSON number to a SQLFLOAT64 value. |
FLOAT64_ARRAY | Converts a JSON array of numbers to a SQLARRAY<FLOAT64> value. |
FLOAT32 | Converts a JSON number to a SQLFLOAT32 value. |
FLOAT32_ARRAY | Converts a JSON array of numbers to a SQLARRAY<FLOAT32> value. |
INT64 | Converts a JSON number to a SQLINT64 value. |
INT64_ARRAY | Converts a JSON array of numbers to a SQLARRAY<INT64> value. |
JSON_ARRAY | Creates a JSON array. |
JSON_ARRAY_APPEND | Appends JSON data to the end of a JSON array. |
JSON_ARRAY_INSERT | Inserts JSON data into a JSON array. |
JSON_CONTAINS | Checks if a JSON document contains another JSON document. |
JSON_KEYS | Extracts unique JSON keys from a JSON expression. |
JSON_OBJECT | Creates a JSON object. |
JSON_QUERY | Extracts a JSON value and converts it to a SQL JSON-formattedSTRING orJSON value. |
JSON_QUERY_ARRAY | Extracts a JSON array and converts it to a SQLARRAY<JSON-formatted STRING> orARRAY<JSON> value. |
JSON_REMOVE | Produces JSON with the specified JSON data removed. |
JSON_SET | Inserts or replaces JSON data. |
JSON_STRIP_NULLS | Removes JSON nulls from JSON objects and JSON arrays. |
JSON_TYPE | Gets the JSON type of the outermost JSON value and converts the name of this type to a SQLSTRING value. |
JSON_VALUE | Extracts a JSON scalar value and converts it to a SQLSTRING value. |
JSON_VALUE_ARRAY | Extracts a JSON array of scalar values and converts it to a SQLARRAY<STRING> value. |
LAX_BOOL | Attempts to convert a JSON value to a SQLBOOL value. |
LAX_FLOAT64 | Attempts to convert a JSON value to a SQLFLOAT64 value. |
LAX_INT64 | Attempts to convert a JSON value to a SQLINT64 value. |
LAX_STRING | Attempts to convert a JSON value to a SQLSTRING value. |
PARSE_JSON | Converts a JSON-formattedSTRING value to aJSON value. |
SAFE_TO_JSON | Similar to the `TO_JSON` function, but for each unsupported field in the input argument, produces a JSON null instead of an error. |
STRING (JSON) | Converts a JSON string to a SQLSTRING value. |
STRING_ARRAY | Converts a JSON array of strings to a SQLARRAY<STRING> value. |
TO_JSON | Converts a SQL value to a JSON value. |
TO_JSON_STRING | Converts aJSON value to a SQL JSON-formattedSTRING value. |
BOOL
BOOL(json_expr)Description
Converts a JSON boolean to a SQLBOOL value.
Arguments:
json_expr: JSON. For example:JSON'true'If the JSON value isn't a boolean, an error is produced. If the expressionis SQL
NULL, the function returns SQLNULL.
Return type
BOOL
Examples
SELECTBOOL(JSON'true')ASvacancy;/*---------+ | vacancy | +---------+ | true | +---------*/SELECTBOOL(JSON_QUERY(JSON'{"hotel class": "5-star", "vacancy": true}',"$.vacancy"))ASvacancy;/*---------+ | vacancy | +---------+ | true | +---------*/The following examples show how invalid requests are handled:
-- An error is thrown if JSON isn't of type bool.SELECTBOOL(JSON'123')ASresult;-- Throws an errorSELECTBOOL(JSON'null')ASresult;-- Throws an errorSELECTSAFE.BOOL(JSON'123')ASresult;-- Returns a SQL NULLBOOL_ARRAY
BOOL_ARRAY(json_expr)Description
Converts a JSON array of booleans to a SQLARRAY<BOOL> value.
Arguments:
json_expr: JSON. For example:JSON'[true]'If the JSON value isn't an array of booleans, an error is produced. If theexpression is SQL
NULL, the function returns SQLNULL.
Return type
ARRAY<BOOL>
Examples
SELECTBOOL_ARRAY(JSON'[true, false]')ASvacancies;/*---------------+ | vacancies | +---------------+ | [true, false] | +---------------*/The following examples show how invalid requests are handled:
-- An error is thrown if the JSON isn't an array of booleans.SELECTBOOL_ARRAY(JSON'[123]')ASresult;-- Throws an errorSELECTBOOL_ARRAY(JSON'[null]')ASresult;-- Throws an errorSELECTBOOL_ARRAY(JSON'null')ASresult;-- Throws an errorFLOAT64
FLOAT64(json_expr[,wide_number_mode=>{'exact'|'round'}])Description
Converts a JSON number to a SQLFLOAT64 value.
Arguments:
json_expr: JSON. For example:JSON'9.8'If the JSON value isn't a number, an error is produced. If the expressionis a SQL
NULL, the function returns SQLNULL.wide_number_mode: A named argument with aSTRINGvalue.Defines what happens with a number that can't berepresented as aFLOAT64without loss ofprecision. This argument accepts one of the two case-sensitive values:exact: The function fails if the result can't be represented as aFLOAT64without loss of precision.round(default): The numeric value stored in JSON will be rounded toFLOAT64. If such rounding isn't possible,the function fails.
Return type
FLOAT64
Examples
SELECTFLOAT64(JSON'9.8')ASvelocity;/*----------+ | velocity | +----------+ | 9.8 | +----------*/SELECTFLOAT64(JSON_QUERY(JSON'{"vo2_max": 39.1, "age": 18}',"$.vo2_max"))ASvo2_max;/*---------+ | vo2_max | +---------+ | 39.1 | +---------*/SELECTFLOAT64(JSON'18446744073709551615',wide_number_mode=>'round')asresult;/*------------------------+ | result | +------------------------+ | 1.8446744073709552e+19 | +------------------------*/SELECTFLOAT64(JSON'18446744073709551615')asresult;/*------------------------+ | result | +------------------------+ | 1.8446744073709552e+19 | +------------------------*/The following examples show how invalid requests are handled:
-- An error is thrown if JSON isn't of type FLOAT64.SELECTFLOAT64(JSON'"strawberry"')ASresult;SELECTFLOAT64(JSON'null')ASresult;-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".SELECTFLOAT64(JSON'123.4',wide_number_mode=>'EXACT')asresult;SELECTFLOAT64(JSON'123.4',wide_number_mode=>'exac')asresult;-- An error is thrown because the number can't be converted to DOUBLE without loss of precisionSELECTFLOAT64(JSON'18446744073709551615',wide_number_mode=>'exact')asresult;-- Returns a SQL NULLSELECTSAFE.FLOAT64(JSON'"strawberry"')ASresult;FLOAT64_ARRAY
FLOAT64_ARRAY(json_expr[,wide_number_mode=>{'exact'|'round'}])Description
Converts a JSON array of numbers to a SQLARRAY<FLOAT64> value.
Arguments:
json_expr: JSON. For example:JSON'[9.8]'If the JSON value isn't an array of numbers, an error is produced. If theexpression is a SQL
NULL, the function returns SQLNULL.wide_number_mode: A named argument that takes aSTRINGvalue. Defineswhat happens with a number that can't be represented as aFLOAT64without loss of precision. This argument acceptsone of the two case-sensitive values:exact: The function fails if the result can't be represented as aFLOAT64without loss of precision.round(default): The numeric value stored in JSON will be rounded toFLOAT64. If such rounding isn't possible, thefunction fails.
Return type
ARRAY<FLOAT64>
Examples
SELECTFLOAT64_ARRAY(JSON'[9, 9.8]')ASvelocities;/*-------------+ | velocities | +-------------+ | [9.0, 9.8] | +-------------*/SELECTFLOAT64_ARRAY(JSON'[18446744073709551615]',wide_number_mode=>'round')asresult;/*--------------------------+ | result | +--------------------------+ | [1.8446744073709552e+19] | +--------------------------*/SELECTFLOAT64_ARRAY(JSON'[18446744073709551615]')asresult;/*--------------------------+ | result | +--------------------------+ | [1.8446744073709552e+19] | +--------------------------*/The following examples show how invalid requests are handled:
-- An error is thrown if the JSON isn't an array of numbers.SELECTFLOAT64_ARRAY(JSON'["strawberry"]')ASresult;SELECTFLOAT64_ARRAY(JSON'[null]')ASresult;SELECTFLOAT64_ARRAY(JSON'null')ASresult;-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".SELECTFLOAT64_ARRAY(JSON'[123.4]',wide_number_mode=>'EXACT')asresult;SELECTFLOAT64_ARRAY(JSON'[123.4]',wide_number_mode=>'exac')asresult;-- An error is thrown because the number can't be converted to DOUBLE without loss of precisionSELECTFLOAT64_ARRAY(JSON'[18446744073709551615]',wide_number_mode=>'exact')asresult;FLOAT32
FLOAT32(json_expr[,[wide_number_mode=>]{'exact'|'round'}])Description
Converts a JSON number to a SQLFLOAT32 value.
Arguments:
json_expr: JSON. For example:JSON'9.8'If the JSON value isn't a number, an error is produced. If the expressionis a SQL
NULL, the function returns SQLNULL.wide_number_mode: A named argument with aSTRINGvalue. Defines whathappens with a number that can't be represented as aFLOAT32without loss of precision. This argument acceptsone of the two case-sensitive values:exact: The function fails if the result can't be represented as aFLOAT32without loss of precision.round(default): The numeric value stored in JSON will be rounded toFLOAT32. If such rounding isn't possible, the functionfails.
Return type
FLOAT32
Examples
SELECTFLOAT32(JSON'9.8')ASvelocity;/*----------+ | velocity | +----------+ | 9.8 | +----------*/SELECTFLOAT32(JSON_QUERY(JSON'{"vo2_max": 39.1, "age": 18}',"$.vo2_max"))ASvo2_max;/*---------+ | vo2_max | +---------+ | 39.1 | +---------*/SELECTFLOAT32(JSON'16777217',wide_number_mode=>'round')asresult;/*------------+ | result | +------------+ | 16777216.0 | +------------*/SELECTFLOAT32(JSON'16777216')asresult;/*------------+ | result | +------------+ | 16777216.0 | +------------*/The following examples show how invalid requests are handled:
-- An error is thrown if JSON isn't of type FLOAT32.SELECTFLOAT32(JSON'"strawberry"')ASresult;SELECTFLOAT32(JSON'null')ASresult;-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".SELECTFLOAT32(JSON'123.4',wide_number_mode=>'EXACT')asresult;SELECTFLOAT32(JSON'123.4',wide_number_mode=>'exac')asresult;-- An error is thrown because the number can't be converted to FLOAT without loss of precisionSELECTFLOAT32(JSON'16777217',wide_number_mode=>'exact')asresult;-- Returns a SQL NULLSELECTSAFE.FLOAT32(JSON'"strawberry"')ASresult;FLOAT32_ARRAY
FLOAT32_ARRAY(json_expr[,wide_number_mode=>{'exact'|'round'}])Description
Converts a JSON array of numbers to a SQLARRAY<FLOAT32> value.
Arguments:
json_expr: JSON. For example:JSON'[9.8]'If the JSON value isn't an array of numbers, an error is produced. If theexpression is a SQL
NULL, the function returns SQLNULL.wide_number_mode: A named argument with aSTRINGvalue. Defineswhat happens with a number that can't be represented as aFLOAT32without loss of precision. This argument acceptsone of the two case-sensitive values:exact: The function fails if the result can't be represented as aFLOAT32without loss of precision.round(default): The numeric value stored in JSON will be rounded toFLOAT32. If such rounding isn't possible, the functionfails.
Return type
ARRAY<FLOAT32>
Examples
SELECTFLOAT32_ARRAY(JSON'[9, 9.8]')ASvelocities;/*-------------+ | velocities | +-------------+ | [9.0, 9.8] | +-------------*/SELECTFLOAT32_ARRAY(JSON'[16777217]',wide_number_mode=>'round')asresult;/*--------------+ | result | +--------------+ | [16777216.0] | +--------------*/SELECTFLOAT32_ARRAY(JSON'[16777216]')asresult;/*--------------+ | result | +--------------+ | [16777216.0] | +--------------*/The following examples show how invalid requests are handled:
-- An error is thrown if the JSON isn't an array of numbers in FLOAT32 domain.SELECTFLOAT32_ARRAY(JSON'["strawberry"]')ASresult;SELECTFLOAT32_ARRAY(JSON'[null]')ASresult;SELECTFLOAT32_ARRAY(JSON'null')ASresult;-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".SELECTFLOAT32_ARRAY(JSON'[123.4]',wide_number_mode=>'EXACT')asresult;SELECTFLOAT32_ARRAY(JSON'[123.4]',wide_number_mode=>'exac')asresult;-- An error is thrown because the number can't be converted to FLOAT without loss of precisionSELECTFLOAT32_ARRAY(JSON'[16777217]',wide_number_mode=>'exact')asresult;INT64
INT64(json_expr)Description
Converts a JSON number to a SQLINT64 value.
Arguments:
json_expr: JSON. For example:JSON'999'If the JSON value isn't a number, or the JSON number isn't in the SQL
INT64domain, an error is produced. If the expression is SQLNULL, thefunction returns SQLNULL.
Return type
INT64
Examples
SELECTINT64(JSON'2005')ASflight_number;/*---------------+ | flight_number | +---------------+ | 2005 | +---------------*/SELECTINT64(JSON_QUERY(JSON'{"gate": "A4", "flight_number": 2005}',"$.flight_number"))ASflight_number;/*---------------+ | flight_number | +---------------+ | 2005 | +---------------*/SELECTINT64(JSON'10.0')ASscore;/*-------+ | score | +-------+ | 10 | +-------*/The following examples show how invalid requests are handled:
-- An error is thrown if JSON isn't a number or can't be converted to a 64-bit integer.SELECTINT64(JSON'10.1')ASresult;-- Throws an errorSELECTINT64(JSON'"strawberry"')ASresult;-- Throws an errorSELECTINT64(JSON'null')ASresult;-- Throws an errorSELECTSAFE.INT64(JSON'"strawberry"')ASresult;-- Returns a SQL NULLINT64_ARRAY
INT64_ARRAY(json_expr)Description
Converts a JSON array of numbers to a SQLINT64_ARRAY value.
Arguments:
json_expr: JSON. For example:JSON'[999]'If the JSON value isn't an array of numbers, or the JSON numbers aren't inthe SQL
INT64domain, an error is produced. If the expression is SQLNULL, the function returns SQLNULL.
Return type
ARRAY<INT64>
Examples
SELECTINT64_ARRAY(JSON'[2005, 2003]')ASflight_numbers;/*----------------+ | flight_numbers | +----------------+ | [2005, 2003] | +----------------*/SELECTINT64_ARRAY(JSON'[10.0]')ASscores;/*--------+ | scores | +--------+ | [10] | +--------*/The following examples show how invalid requests are handled:
-- An error is thrown if the JSON isn't an array of numbers in INT64 domain.SELECTINT64_ARRAY(JSON'[10.1]')ASresult;-- Throws an errorSELECTINT64_ARRAY(JSON'["strawberry"]')ASresult;-- Throws an errorSELECTINT64_ARRAY(JSON'[null]')ASresult;-- Throws an errorSELECTINT64_ARRAY(JSON'null')ASresult;-- Throws an errorJSON_ARRAY
JSON_ARRAY([value][,...])Description
Creates a JSON array from zero or more SQL values.
Arguments:
value: AJSON encoding-supported value to addto a JSON array.
Return type
JSON
Examples
The following query creates a JSON array with one value in it:
SELECTJSON_ARRAY(10)ASjson_data/*-----------+ | json_data | +-----------+ | [10] | +-----------*/You can create a JSON array with an empty JSON array in it. For example:
SELECTJSON_ARRAY([])ASjson_data/*-----------+ | json_data | +-----------+ | [[]] | +-----------*/SELECTJSON_ARRAY(10,'foo',NULL)ASjson_data/*-----------------+ | json_data | +-----------------+ | [10,"foo",null] | +-----------------*/SELECTJSON_ARRAY(STRUCT(10ASa,'foo'ASb))ASjson_data/*----------------------+ | json_data | +----------------------+ | [{"a":10,"b":"foo"}] | +----------------------*/SELECTJSON_ARRAY(10,['foo','bar'],[20,30])ASjson_data/*----------------------------+ | json_data | +----------------------------+ | [10,["foo","bar"],[20,30]] | +----------------------------*/SELECTJSON_ARRAY(10,[JSON'20',JSON'"foo"'])ASjson_data/*-----------------+ | json_data | +-----------------+ | [10,[20,"foo"]] | +-----------------*/You can create an empty JSON array. For example:
SELECTJSON_ARRAY()ASjson_data/*-----------+ | json_data | +-----------+ | [] | +-----------*/JSON_ARRAY_APPEND
JSON_ARRAY_APPEND(json_expr,json_path_value_pair[,...][,append_each_element=>{TRUE|FALSE}])json_path_value_pair:json_path,valueAppends JSON data to the end of a JSON array.
Arguments:
json_expr: JSON. For example:JSON'["a", "b", "c"]'json_path_value_pair: A value and theJSONPath forthat value. This includes:json_path: Appendvalueat thisJSONPathinjson_expr.value: AJSON encoding-supported value toappend.
append_each_element: A named argument with aBOOLvalue.If
TRUE(default), andvalueis a SQL array,appends each element individually.If
FALSE,andvalueis a SQL array, appendsthe array as one element.
Details:
- Path value pairs are evaluated left to right. The JSON produced byevaluating one pair becomes the JSON against which the next pairis evaluated.
- The operation is ignored if the path points to a JSON non-array value thatisn't a JSON null.
- If
json_pathpoints to a JSON null, the JSON null is replaced by aJSON array that containsvalue. - If the path exists but has an incompatible type at any given path token,the path value pair operation is ignored.
- The function applies all path value pair append operations even if anindividual path value pair operation is invalid. For invalid operations,the operation is ignored and the function continues to process the rest ofthe path value pairs.
- If any
json_pathis an invalidJSONPath, an error isproduced. - If
json_expris SQLNULL, the function returns SQLNULL. - If
append_each_elementis SQLNULL, the function returnsjson_expr. - If
json_pathis SQLNULL, thejson_path_value_pairoperation isignored.
Return type
JSON
Examples
In the following example, path$ is matched and appends1.
SELECTJSON_ARRAY_APPEND(JSON'["a", "b", "c"]','$',1)ASjson_data/*-----------------+ | json_data | +-----------------+ | ["a","b","c",1] | +-----------------*/In the following example,append_each_element defaults toTRUE, so[1, 2] is appended as individual elements.
SELECTJSON_ARRAY_APPEND(JSON'["a", "b", "c"]','$',[1,2])ASjson_data/*-------------------+ | json_data | +-------------------+ | ["a","b","c",1,2] | +-------------------*/In the following example,append_each_element isFALSE, so[1, 2] is appended as one element.
SELECTJSON_ARRAY_APPEND(JSON'["a", "b", "c"]','$',[1,2],append_each_element=>FALSE)ASjson_data/*---------------------+ | json_data | +---------------------+ | ["a","b","c",[1,2]] | +---------------------*/In the following example,append_each_element isFALSE, so[1, 2] and[3, 4] are each appended as one element.
SELECTJSON_ARRAY_APPEND(JSON'["a", ["b"], "c"]','$[1]',[1,2],'$[1][1]',[3,4],append_each_element=>FALSE)ASjson_data/*-----------------------------+ | json_data | +-----------------------------+ | ["a",["b",[1,2,[3,4]]],"c"] | +-----------------------------*/In the following example, the first path$[1] appends[1, 2] as singleelements, and then the second path$[1][1] isn't a valid path to an array,so the second operation is ignored.
SELECTJSON_ARRAY_APPEND(JSON'["a", ["b"], "c"]','$[1]',[1,2],'$[1][1]',[3,4])ASjson_data/*---------------------+ | json_data | +---------------------+ | ["a",["b",1,2],"c"] | +---------------------*/In the following example, path$.a is matched and appends2.
SELECTJSON_ARRAY_APPEND(JSON'{"a": [1]}','$.a',2)ASjson_data/*-------------+ | json_data | +-------------+ | {"a":[1,2]} | +-------------*/In the following example, a value is appended into a JSON null.
SELECTJSON_ARRAY_APPEND(JSON'{"a": null}','$.a',10)/*------------+ | json_data | +------------+ | {"a":[10]} | +------------*/In the following example, path$.a isn't an array, so the operation isignored.
SELECTJSON_ARRAY_APPEND(JSON'{"a": 1}','$.a',2)ASjson_data/*-----------+ | json_data | +-----------+ | {"a":1} | +-----------*/In the following example, path$.b doesn't exist, so the operation isignored.
SELECTJSON_ARRAY_APPEND(JSON'{"a": 1}','$.b',2)ASjson_data/*-----------+ | json_data | +-----------+ | {"a":1} | +-----------*/JSON_ARRAY_INSERT
JSON_ARRAY_INSERT(json_expr,json_path_value_pair[,...][,insert_each_element=>{TRUE|FALSE}])json_path_value_pair:json_path,valueProduces a new JSON value that's created by inserting JSON data intoa JSON array.
Arguments:
json_expr: JSON. For example:JSON'["a", "b", "c"]'json_path_value_pair: A value and theJSONPath forthat value. This includes:json_path: Insertvalueat thisJSONPathinjson_expr.value: AJSON encoding-supported value toinsert.
insert_each_element: A named argument with aBOOLvalue.If
TRUE(default), andvalueis a SQL array,inserts each element individually.If
FALSE,andvalueis a SQL array, insertsthe array as one element.
Details:
- Path value pairs are evaluated left to right. The JSON produced byevaluating one pair becomes the JSON against which the next pairis evaluated.
- The operation is ignored if the path points to a JSON non-array value thatisn't a JSON null.
- If
json_pathpoints to a JSON null, the JSON null is replaced by aJSON array of the appropriate size and padded on the left with JSON nulls. - If the path exists but has an incompatible type at any given path token,the path value pair operator is ignored.
- The function applies all path value pair append operations even if anindividual path value pair operation is invalid. For invalid operations,the operation is ignored and the function continues to process the rest ofthe path value pairs.
- If the array index in
json_pathis larger than the size of the array, thefunction extends the length of the array to the index, fills inthe array with JSON nulls, then addsvalueat the index. - If any
json_pathis an invalidJSONPath, an error isproduced. - If
json_expris SQLNULL, the function returns SQLNULL. - If
insert_each_elementis SQLNULL, the function returnsjson_expr. - If
json_pathis SQLNULL, thejson_path_value_pairoperation isignored.
Return type
JSON
Examples
In the following example, path$[1] is matched and inserts1.
SELECTJSON_ARRAY_INSERT(JSON'["a", ["b", "c"], "d"]','$[1]',1)ASjson_data/*-----------------------+ | json_data | +-----------------------+ | ["a",1,["b","c"],"d"] | +-----------------------*/In the following example, path$[1][0] is matched and inserts1.
SELECTJSON_ARRAY_INSERT(JSON'["a", ["b", "c"], "d"]','$[1][0]',1)ASjson_data/*-----------------------+ | json_data | +-----------------------+ | ["a",[1,"b","c"],"d"] | +-----------------------*/In the following example,insert_each_element defaults toTRUE, so[1, 2] is inserted as individual elements.
SELECTJSON_ARRAY_INSERT(JSON'["a", "b", "c"]','$[1]',[1,2])ASjson_data/*-------------------+ | json_data | +-------------------+ | ["a",1,2,"b","c"] | +-------------------*/In the following example,insert_each_element isFALSE, so[1, 2] isinserted as one element.
SELECTJSON_ARRAY_INSERT(JSON'["a", "b", "c"]','$[1]',[1,2],insert_each_element=>FALSE)ASjson_data/*---------------------+ | json_data | +---------------------+ | ["a",[1,2],"b","c"] | +---------------------*/In the following example, path$[7] is larger than the length of thematched array, so the array is extended with JSON nulls and"e" is inserted atthe end of the array.
SELECTJSON_ARRAY_INSERT(JSON'["a", "b", "c", "d"]','$[7]',"e")ASjson_data/*--------------------------------------+ | json_data | +--------------------------------------+ | ["a","b","c","d",null,null,null,"e"] | +--------------------------------------*/In the following example, path$.a is an object, so the operation is ignored.
SELECTJSON_ARRAY_INSERT(JSON'{"a": {}}','$.a[0]',2)ASjson_data/*-----------+ | json_data | +-----------+ | {"a":{}} | +-----------*/In the following example, path$ doesn't specify a valid array position,so the operation is ignored.
SELECTJSON_ARRAY_INSERT(JSON'[1, 2]','$',3)ASjson_data/*-----------+ | json_data | +-----------+ | [1,2] | +-----------*/In the following example, a value is inserted into a JSON null.
SELECTJSON_ARRAY_INSERT(JSON'{"a": null}','$.a[2]',10)ASjson_data/*----------------------+ | json_data | +----------------------+ | {"a":[null,null,10]} | +----------------------*/In the following example, the operation is ignored because you can't insertdata into a JSON number.
SELECTJSON_ARRAY_INSERT(JSON'1','$[0]','r1')ASjson_data/*-----------+ | json_data | +-----------+ | 1 | +-----------*/JSON_CONTAINS
JSON_CONTAINS(json_expr,json_expr)Description
Checks if a JSON document contains another JSON document. This function returnstrue if the first parameter JSON document contains the second parameter JSONdocument; otherwise the function returnsfalse. If any input argument isNULL, aNULL value is returned.
Arguments:
json_expr: JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'
Details:
- The structure and data of the contained document must match a portion of thecontaining document. This function determines if the smaller JSON documentis part of the larger JSON document.
- JSON scalars: A JSON scalar value (like a string, number, bool, or JSON null) contains only itself.
JSON objects:
- An object contains another object if the first object contains all thekey-value pairs present in the second JSON object.
- When checking for object containment, extra key-value pairs in thecontaining object don't prevent a match.
- Any JSON object can contain an empty object.
JSON arrays:
- An array contains another array if every element of the second array iscontained by some element of the first.
- Duplicate elements in arrays are treated as if they appear only once.
- The order of elements within JSON arrays isn't significant forcontainment checks.
- Any array can contain an empty array.
- As a special case, a top-level array can contain a scalar value.
Return type
BOOL
Examples
In the following example, a JSON scalar value (a string) contains only itself:
SELECTJSON_CONTAINS(JSON'"a"',JSON'"a"')ASresult;/*----------+ | result | +----------+ | true | +----------*/The following examples check if a JSON object contains another JSON object:
SELECTJSON_CONTAINS(JSON'{"a": {"b": 1}, "c": 2}',JSON'{"b": 1}')ASresult1,JSON_CONTAINS(JSON'{"a": {"b": 1}, "c": 2}',JSON'{"a": {"b": 1}}')ASresult2,JSON_CONTAINS(JSON'{"a": {"b": 1, "d": 3}, "c": 2}',JSON'{"a": {"b": 1}}')ASresult3;/*----------*----------*----------+ | result1 | result2 | result3 | +----------+----------+----------+ | false | true | true | +----------*----------*----------*/The following examples check if a JSON array contains another JSON array. Anarray contains another array if the first JSON array contains all the elementspresent in the second array. The order of elements doesn't matter.
Also, if the array is a top-level array, it can contain a scalar value.
SELECTJSON_CONTAINS(JSON'[1, 2, 3]',JSON'[2]')ASresult1,JSON_CONTAINS(JSON'[1, 2, 3]',JSON'2')ASresult2;/*----------*----------+ | result1 | result2 | +----------+----------+ | true | true | +----------*----------*/SELECTJSON_CONTAINS(JSON'[[1, 2, 3]]',JSON'2')ASresult1,JSON_CONTAINS(JSON'[[1, 2, 3]]',JSON'[2]')ASresult2,JSON_CONTAINS(JSON'[[1, 2, 3]]',JSON'[[2]]')ASresult3;/*----------*----------*----------+ | result1 | result2 | result3 | +----------+----------+----------+ | false | false | true | +----------*----------*----------*/The following examples check if a JSON array contains a JSON object:
SELECTJSON_CONTAINS(JSON'[{"a":0}, {"b":1, "c":2}]',JSON'[{"b":1}]')ASresult1,JSON_CONTAINS(JSON'[{"a":0}, {"b":1, "c":2}]',JSON'{"b":1}')ASresults2,JSON_CONTAINS(JSON'[{"a":0}, {"b":1, "c":2}]',JSON'[{"a":0, "b":1}]')ASresults3;/*----------*----------*----------+ | result1 | result2 | result3 | +----------+----------+----------+ | true | false | false | +----------*----------*----------*/JSON_KEYS
JSON_KEYS(json_expr[,max_depth][,mode=>{'strict'|'lax'|'lax recursive'}])Description
Extracts unique JSON keys from a JSON expression.
Arguments:
json_expr:JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'max_depth: AnINT64value that represents the maximum depth of nestedfields to search injson_expr. If notset, the function searches the entire JSON document.mode: A named argument with aSTRINGvalue that can be one of thefollowing:strict(default): Ignore any key that appears in an array.lax: Also include keys contained in non-consecutively nested arrays.lax recursive: Return all keys.
Details:
- Keys are de-duplicated and returned in alphabetical order.
- Keys don't include array indices.
- Keys containing special characters are escaped using double quotes.
- Keys are case sensitive and not normalized.
- If
json_exprormodeis SQLNULL, the function returns SQLNULL. - If
max_depthis SQLNULL, the function ignores the argument. - If
max_depthis less than or equal to 0, then an error is returned.
Return type
ARRAY<STRING>
Examples
In the following example, there are no arrays, so all keys are returned.
SELECTJSON_KEYS(JSON'{"a": {"b":1}}')ASjson_keys/*-----------+ | json_keys | +-----------+ | [a, a.b] | +-----------*/In the following example,max_depth is set to 1 so "a.b" isn't included.
SELECTJSON_KEYS(JSON'{"a": {"b":1}}',1)ASjson_keys/*-----------+ | json_keys | +-----------+ | [a] | +-----------*/In the following example, thejson_expr argument contains an array. Becausethe mode isstrict, keys inside the array are excluded.
SELECTJSON_KEYS(JSON'{"a":[{"b":1}, {"c":2}], "d":3}')ASjson_keys/*-----------+ | json_keys | +-----------+ | [a, d] | +-----------*/In the following example, thejson_expr argument contains an array. Becausethe mode islax, keys inside the array are included.
SELECTJSON_KEYS(JSON'{"a":[{"b":1}, {"c":2}], "d":3}',mode=>"lax")asjson_keys/*------------------+ | json_keys | +------------------+ | [a, a.b, a.c, d] | +------------------*/In the following example, thejson_expr argument contains consecutively nestedarrays. Because the mode islax, keys inside the consecutively nested arraysaren't included.
SELECTJSON_KEYS(JSON'{"a":[[{"b":1}]]}',mode=>"lax")asjson_keys/*-----------+ | json_keys | +-----------+ | [a] | +-----------*/In the following example, thejson_expr argument contains consecutively nestedarrays. Because the mode islax recursive, every key is returned.
SELECTJSON_KEYS(JSON'{"a":[[{"b":1}]]}',mode=>"lax recursive")asjson_keys/*-----------+ | json_keys | +-----------+ | [a, a.b] | +-----------*/In the following example, thejson_expr argument contains multiple arrays.Because the arrays aren't consecutively nested and the mode islax, keysinside the arrays are included.
SELECTJSON_KEYS(JSON'{"a":[{"b":[{"c":1}]}]}',mode=>"lax")asjson_keys/*-----------------+ | json_keys | +-----------------+ | [a, a.b, a.b.c] | +-----------------*/In the following example, thejson_expr argument contains both consecutivelynested and single arrays. Because the mode islax, keys inside theconsecutively nested arrays are excluded.
SELECTJSON_KEYS(JSON'{"a":[{"b":[[{"c":1}]]}]}',mode=>"lax")asjson_keys/*-----------+ | json_keys | +-----------+ | [a, a.b] | +-----------*/In the following example, thejson_expr argument contains both consecutivelynested and single arrays. Because the mode islax recursive, all keys areincluded.
SELECTJSON_KEYS(JSON'{"a":[{"b":[[{"c":1}]]}]}',mode=>"lax recursive")asjson_keys/*-----------------+ | json_keys | +-----------------+ | [a, a.b, a.b.c] | +-----------------*/JSON_OBJECT
- Signature 1:
JSON_OBJECT([json_key, json_value][, ...]) - Signature 2:
JSON_OBJECT(json_key_array, json_value_array)
Signature 1
JSON_OBJECT([json_key,json_value][,...])Description
Creates a JSON object, using key-value pairs.
Arguments:
json_key: ASTRINGvalue that represents a key.json_value: AJSON encoding-supported value.
Details:
- If two keys are passed in with the same name, only the first key-value pairis preserved.
- The order of key-value pairs isn't preserved.
- If
json_keyisNULL, an error is produced.
Return type
JSON
Examples
You can create an empty JSON object by passing in no JSON keys and values.For example:
SELECTJSON_OBJECT()ASjson_data/*-----------+ | json_data | +-----------+ | {} | +-----------*/You can create a JSON object by passing in key-value pairs. For example:
SELECTJSON_OBJECT('foo',10,'bar',TRUE)ASjson_data/*-----------------------+ | json_data | +-----------------------+ | {"bar":true,"foo":10} | +-----------------------*/SELECTJSON_OBJECT('foo',10,'bar',['a','b'])ASjson_data/*----------------------------+ | json_data | +----------------------------+ | {"bar":["a","b"],"foo":10} | +----------------------------*/SELECTJSON_OBJECT('a',NULL,'b',JSON'null')ASjson_data/*---------------------+ | json_data | +---------------------+ | {"a":null,"b":null} | +---------------------*/SELECTJSON_OBJECT('a',10,'a','foo')ASjson_data/*-----------+ | json_data | +-----------+ | {"a":10} | +-----------*/WITHItemsAS(SELECT'hello'ASkey,'world'ASvalue)SELECTJSON_OBJECT(key,value)ASjson_dataFROMItems/*-------------------+ | json_data | +-------------------+ | {"hello":"world"} | +-------------------*/An error is produced if a SQLNULL is passed in for a JSON key.
-- Error: A key can't be NULL.SELECTJSON_OBJECT(NULL,1)ASjson_dataAn error is produced if the number of JSON keys and JSON values don't match:
-- Error: No matching signature for function JSON_OBJECT for argument types:-- STRING, INT64, STRINGSELECTJSON_OBJECT('a',1,'b')ASjson_dataSignature 2
JSON_OBJECT(json_key_array,json_value_array)Creates a JSON object, using an array of keys and values.
Arguments:
json_key_array: An array of zero or moreSTRINGkeys.json_value_array: An array of zero or moreJSON encoding-supported values.
Details:
- If two keys are passed in with the same name, only the first key-value pairis preserved.
- The order of key-value pairs isn't preserved.
- The number of keys must match the number of values, otherwise an error isproduced.
- If any argument is
NULL, an error is produced. - If a key in
json_key_arrayisNULL, an error is produced.
Return type
JSON
Examples
You can create an empty JSON object by passing in an empty array ofkeys and values. For example:
SELECTJSON_OBJECT(CAST([]ASARRAY<STRING>),[])ASjson_data/*-----------+ | json_data | +-----------+ | {} | +-----------*/You can create a JSON object by passing in an array of keys and an array ofvalues. For example:
SELECTJSON_OBJECT(['a','b'],[10,NULL])ASjson_data/*-------------------+ | json_data | +-------------------+ | {"a":10,"b":null} | +-------------------*/SELECTJSON_OBJECT(['a','b'],[JSON'10',JSON'"foo"'])ASjson_data/*--------------------+ | json_data | +--------------------+ | {"a":10,"b":"foo"} | +--------------------*/SELECTJSON_OBJECT(['a','b'],[STRUCT(10ASid,'Red'AScolor),STRUCT(20ASid,'Blue'AScolor)])ASjson_data/*------------------------------------------------------------+ | json_data | +------------------------------------------------------------+ | {"a":{"color":"Red","id":10},"b":{"color":"Blue","id":20}} | +------------------------------------------------------------*/SELECTJSON_OBJECT(['a','b'],[TO_JSON(10),TO_JSON(['foo','bar'])])ASjson_data/*----------------------------+ | json_data | +----------------------------+ | {"a":10,"b":["foo","bar"]} | +----------------------------*/The following query groups byid and then creates an array of keys andvalues from the rows with the sameid:
WITHFruitsAS(SELECT0ASid,'color'ASjson_key,'red'ASjson_valueUNIONALLSELECT0,'fruit','apple'UNIONALLSELECT1,'fruit','banana'UNIONALLSELECT1,'ripe','true')SELECTJSON_OBJECT(ARRAY_AGG(json_key),ARRAY_AGG(json_value))ASjson_dataFROMFruitsGROUPBYid/*----------------------------------+ | json_data | +----------------------------------+ | {"color":"red","fruit":"apple"} | | {"fruit":"banana","ripe":"true"} | +----------------------------------*/An error is produced if the size of the JSON keys and values arrays don'tmatch:
-- Error: The number of keys and values must match.SELECTJSON_OBJECT(['a','b'],[10])ASjson_dataAn error is produced if the array of JSON keys or JSON values is a SQLNULL.
-- Error: The keys array can't be NULL.SELECTJSON_OBJECT(CAST(NULLASARRAY<STRING>),[10,20])ASjson_data-- Error: The values array can't be NULL.SELECTJSON_OBJECT(['a','b'],CAST(NULLASARRAY<INT64>))ASjson_dataJSON_QUERY
JSON_QUERY(json_string_expr,json_path)JSON_QUERY(json_expr,json_path)Description
Extracts a JSON value and converts it to a SQLJSON-formattedSTRING orJSON value.This function uses double quotes to escape invalidJSONPath characters in JSON keys. For example:"a.b".
Arguments:
json_string_expr: A JSON-formatted string. For example:'{"class": {"students": [{"name": "Jane"}]}}'Extracts a SQL
NULLwhen a JSON-formatted stringnullis encountered.For example:SELECTJSON_QUERY("null","$")-- Returns a SQL NULLjson_expr: JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'Extracts a JSON
nullwhen a JSONnullis encountered.SELECTJSON_QUERY(JSON'null',"$")-- Returns a JSON 'null'json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input.
There are differences between the JSON-formatted string and JSON input types.For details, seeDifferences between the JSON and JSON-formatted STRING types.
Return type
json_string_expr: A JSON-formattedSTRINGjson_expr:JSON
Examples
In the following example, JSON data is extracted and returned as JSON.
SELECTJSON_QUERY(JSON'{"class": {"students": [{"id": 5}, {"id": 12}]}}','$.class')ASjson_data;/*-----------------------------------+ | json_data | +-----------------------------------+ | {"students":[{"id":5},{"id":12}]} | +-----------------------------------*/In the following examples, JSON data is extracted and returned asJSON-formatted strings.
SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"Jane"}]}} | +-----------------------------------------------------------*/SELECTJSON_QUERY('{"class": {"students": []}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[]}} | +-----------------------------------------------------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "John"},{"name": "Jamie"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} | +-----------------------------------------------------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student | +-----------------+ | {"name":"Jane"} | +-----------------*/SELECTJSON_QUERY('{"class": {"students": []}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student | +-----------------+ | NULL | +-----------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student | +-----------------+ | {"name":"John"} | +-----------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL | +----------------*/SELECTJSON_QUERY('{"class": {"students": []}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL | +----------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": null}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL | +----------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | "Jamie" | +----------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$.class."students"')ASstudent_names;/*------------------------------------+ | student_names | +------------------------------------+ | [{"name":"Jane"}] | +------------------------------------*/SELECTJSON_QUERY('{"class": {"students": []}}','$.class."students"')ASstudent_names;/*------------------------------------+ | student_names | +------------------------------------+ | [] | +------------------------------------*/SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class."students"')ASstudent_names;/*------------------------------------+ | student_names | +------------------------------------+ | [{"name":"John"},{"name":"Jamie"}] | +------------------------------------*/SELECTJSON_QUERY('{"a": null}',"$.a");-- Returns a SQL NULLSELECTJSON_QUERY('{"a": null}',"$.b");-- Returns a SQL NULLSELECTJSON_QUERY(JSON'{"a": null}',"$.a");-- Returns a JSON 'null'SELECTJSON_QUERY(JSON'{"a": null}',"$.b");-- Returns a SQL NULLJSON_QUERY_ARRAY
JSON_QUERY_ARRAY(json_string_expr[,json_path])JSON_QUERY_ARRAY(json_expr[,json_path])Description
Extracts a JSON array and converts it toa SQLARRAY<JSON-formatted STRING> orARRAY<JSON> value.In addition, this function uses double quotes to escape invalidJSONPath characters in JSON keys. For example:"a.b".
Arguments:
json_string_expr: A JSON-formatted string. For example:'["a", "b", {"key": "c"}]'json_expr: JSON. For example:JSON'["a", "b", {"key": "c"}]'json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input. If this optional parameter isn'tprovided, then the JSONPath$symbol is applied, which means that all ofthe data is analyzed.
There are differences between the JSON-formatted string and JSON input types.For details, seeDifferences between the JSON and JSON-formatted STRING types.
Return type
json_string_expr:ARRAY<JSON-formatted STRING>json_expr:ARRAY<JSON>
Examples
This extracts items in JSON to an array ofJSON values:
SELECTJSON_QUERY_ARRAY(JSON'{"fruits": ["apples", "oranges", "grapes"]}','$.fruits')ASjson_array;/*---------------------------------+ | json_array | +---------------------------------+ | ["apples", "oranges", "grapes"] | +---------------------------------*/This extracts the items in a JSON-formatted string to a string array:
SELECTJSON_QUERY_ARRAY('[1, 2, 3]')ASstring_array;/*--------------+ | string_array | +--------------+ | [1, 2, 3] | +--------------*/This extracts a string array and converts it to an integer array:
SELECTARRAY(SELECTCAST(integer_elementASINT64)FROMUNNEST(JSON_QUERY_ARRAY('[1, 2, 3]','$'))ASinteger_element)ASinteger_array;/*---------------+ | integer_array | +---------------+ | [1, 2, 3] | +---------------*/This extracts string values in a JSON-formatted string to an array:
-- Doesn't strip the double quotesSELECTJSON_QUERY_ARRAY('["apples", "oranges", "grapes"]','$')ASstring_array;/*---------------------------------+ | string_array | +---------------------------------+ | ["apples", "oranges", "grapes"] | +---------------------------------*/-- Strips the double quotesSELECTARRAY(SELECTJSON_VALUE(string_element,'$')FROMUNNEST(JSON_QUERY_ARRAY('["apples", "oranges", "grapes"]','$'))ASstring_element)ASstring_array;/*---------------------------+ | string_array | +---------------------------+ | [apples, oranges, grapes] | +---------------------------*/This extracts only the items in thefruit property to an array:
SELECTJSON_QUERY_ARRAY('{"fruit": [{"apples": 5, "oranges": 10}, {"apples": 2, "oranges": 4}], "vegetables": [{"lettuce": 7, "kale": 8}]}','$.fruit')ASstring_array;/*-------------------------------------------------------+ | string_array | +-------------------------------------------------------+ | [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] | +-------------------------------------------------------*/These are equivalent:
SELECTJSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$.fruits')ASstring_array;SELECTJSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$."fruits"')ASstring_array;-- The queries above produce the following result:/*---------------------------------+ | string_array | +---------------------------------+ | ["apples", "oranges", "grapes"] | +---------------------------------*/In cases where a JSON key uses invalid JSONPath characters, you can escape thosecharacters using double quotes:" ". For example:
SELECTJSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}','$."a.b".c')AShello;/*-----------+ | hello | +-----------+ | ["world"] | +-----------*/The following examples show how invalid requests and empty arrays are handled:
-- An error is returned if you provide an invalid JSONPath.SELECTJSON_QUERY_ARRAY('["foo", "bar", "baz"]','INVALID_JSONPath')ASresult;-- If the JSONPath doesn't refer to an array, then NULL is returned.SELECTJSON_QUERY_ARRAY('{"a": "foo"}','$.a')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If a key that doesn't exist is specified, then the result is NULL.SELECTJSON_QUERY_ARRAY('{"a": "foo"}','$.b')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- Empty arrays in JSON-formatted strings are supported.SELECTJSON_QUERY_ARRAY('{"a": "foo", "b": []}','$.b')ASresult;/*--------+ | result | +--------+ | [] | +--------*/JSON_REMOVE
JSON_REMOVE(json_expr,json_path[,...])Produces a new SQLJSON value with the specified JSON data removed.
Arguments:
json_expr: JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'json_path: Remove data at thisJSONPath injson_expr.
Details:
- Paths are evaluated left to right. The JSON produced by evaluating thefirst path is the JSON for the next path.
- The operation ignores non-existent paths and continue processing the restof the paths.
- For each path, the entire matched JSON subtree is deleted.
- If the path matches a JSON object key, this function deletes thekey-value pair.
- If the path matches an array element, this function deletes the specificelement from the matched array.
- If removing the path results in an empty JSON object or empty JSON array,the empty structure is preserved.
- If
json_pathis$or an invalidJSONPath, an error isproduced. - If
json_pathis SQLNULL, the path operation is ignored.
Return type
JSON
Examples
In the following example, the path$[1] is matched and removes["b", "c"].
SELECTJSON_REMOVE(JSON'["a", ["b", "c"], "d"]','$[1]')ASjson_data/*-----------+ | json_data | +-----------+ | ["a","d"] | +-----------*/You can use the field access operator to pass JSON data into this function.For example:
WITHTAS(SELECTJSON'{"a": {"b": 10, "c": 20}}'ASdata)SELECTJSON_REMOVE(data.a,'$.b')ASjson_dataFROMT/*-----------+ | json_data | +-----------+ | {"c":20} | +-----------*/In the following example, the first path$[1] is matched and removes["b", "c"]. Then, the second path$[1] is matched and removes"d".
SELECTJSON_REMOVE(JSON'["a", ["b", "c"], "d"]','$[1]','$[1]')ASjson_data/*-----------+ | json_data | +-----------+ | ["a"] | +-----------*/The structure of an empty array is preserved when all elements are deletedfrom it. For example:
SELECTJSON_REMOVE(JSON'["a", ["b", "c"], "d"]','$[1]','$[1]','$[0]')ASjson_data/*-----------+ | json_data | +-----------+ | [] | +-----------*/In the following example, the path$.a.b.c is matched and removes the"c":"d" key-value pair from the JSON object.
SELECTJSON_REMOVE(JSON'{"a": {"b": {"c": "d"}}}','$.a.b.c')ASjson_data/*----------------+ | json_data | +----------------+ | {"a":{"b":{}}} | +----------------*/In the following example, the path$.a.b is matched and removes the"b": {"c":"d"} key-value pair from the JSON object.
SELECTJSON_REMOVE(JSON'{"a": {"b": {"c": "d"}}}','$.a.b')ASjson_data/*-----------+ | json_data | +-----------+ | {"a":{}} | +-----------*/In the following example, the path$.b isn't valid, so the operation makesno changes.
SELECTJSON_REMOVE(JSON'{"a": 1}','$.b')ASjson_data/*-----------+ | json_data | +-----------+ | {"a":1} | +-----------*/In the following example, path$.a.b and$.b don't exist, so thoseoperations are ignored, but the others are processed.
SELECTJSON_REMOVE(JSON'{"a": [1, 2, 3]}','$.a[0]','$.a.b','$.b','$.a[0]')ASjson_data/*-----------+ | json_data | +-----------+ | {"a":[3]} | +-----------*/If you pass in$ as the path, an error is produced. For example:
-- Error: The JSONPath can't be '$'SELECTJSON_REMOVE(JSON'{}','$')ASjson_dataIn the following example, the operation is ignored because you can't removedata from a JSON null.
SELECTJSON_REMOVE(JSON'null','$.a.b')ASjson_data/*-----------+ | json_data | +-----------+ | null | +-----------*/JSON_SET
JSON_SET(json_expr,json_path_value_pair[,...][,create_if_missing=>{TRUE|FALSE}])json_path_value_pair:json_path,valueProduces a new SQLJSON value with the specified JSON data insertedor replaced.
Arguments:
json_expr: JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'json_path_value_pair: A value and theJSONPath forthat value. This includes:json_path: Insert or replacevalueat thisJSONPathinjson_expr.value: AJSON encoding-supported value toinsert.
create_if_missing: A named argument that takes aBOOLvalue.If
TRUE(default), replaces or inserts data if the path doesn't exist.If
FALSE, only existing JSONPath values are replaced. If the pathdoesn't exist, the set operation is ignored.
Details:
- Path value pairs are evaluated left to right. The JSON produced byevaluating one pair becomes the JSON against which the next pairis evaluated.
- If a matched path has an existing value, it overwrites the existing datawith
value. If
create_if_missingisTRUE:- If a path doesn't exist, the remainder of the path is recursively created.
- If the matched path prefix points to a JSON null, the remainder of the path is recursively created, and
valueis inserted. - If a path token points to a JSON array and the specified index islarger than the size of the array, pads the JSON array with JSON nulls, recursively creates the remainder of the path at the specified index, and inserts the path value pair.
This function applies all path value pair set operations even if anindividual path value pair operation is invalid. For invalid operations,the operation is ignored and the function continues to process the restof the path value pairs.
If the path exists but has an incompatible type at any given pathtoken, no update happens for that specific path value pair.
If any
json_pathis an invalidJSONPath, an error isproduced.If
json_expris SQLNULL, the function returns SQLNULL.If
json_pathis SQLNULL, thejson_path_value_pairoperation isignored.If
create_if_missingis SQLNULL, the set operation is ignored.
Return type
JSON
Examples
In the following example, the path$ matches the entireJSON valueand replaces it with{"b": 2, "c": 3}.
SELECTJSON_SET(JSON'{"a": 1}','$',JSON'{"b": 2, "c": 3}')ASjson_data/*---------------+ | json_data | +---------------+ | {"b":2,"c":3} | +---------------*/In the following example,create_if_missing isFALSE and the path$.bdoesn't exist, so the set operation is ignored.
SELECTJSON_SET(JSON'{"a": 1}',"$.b",999,create_if_missing=>false)ASjson_data/*------------+ | json_data | +------------+ | '{"a": 1}' | +------------*/In the following example,create_if_missing isTRUE and the path$.aexists, so the value is replaced.
SELECTJSON_SET(JSON'{"a": 1}',"$.a",999,create_if_missing=>false)ASjson_data/*--------------+ | json_data | +--------------+ | '{"a": 999}' | +--------------*/In the following example, the path$.a is matched, but$.a.b doesn'texist, so the new path and the value are inserted.
SELECTJSON_SET(JSON'{"a": {}}','$.a.b',100)ASjson_data/*-----------------+ | json_data | +-----------------+ | {"a":{"b":100}} | +-----------------*/In the following example, the path prefix$ points to a JSON null, so theremainder of the path is created for the value100.
SELECTJSON_SET(JSON'null','$.a.b',100)ASjson_data/*-----------------+ | json_data | +-----------------+ | {"a":{"b":100}} | +-----------------*/In the following example, the path$.a.c implies that the value at$.a isa JSON object but it's not. This part of the operation is ignored, but the otherparts of the operation are completed successfully.
SELECTJSON_SET(JSON'{"a": 1}','$.b',2,'$.a.c',100,'$.d',3)ASjson_data/*---------------------+ | json_data | +---------------------+ | {"a":1,"b":2,"d":3} | +---------------------*/In the following example, the path$.a[2] implies that the value for$.a isan array, but it's not, so the operation is ignored for that value.
SELECTJSON_SET(JSON'{"a": 1}','$.a[2]',100,'$.b',2)ASjson_data/*---------------+ | json_data | +---------------+ | {"a":1,"b":2} | +---------------*/In the following example, the path$[1] is matched and replaces thearray element value withfoo.
SELECTJSON_SET(JSON'["a", ["b", "c"], "d"]','$[1]',"foo")ASjson_data/*-----------------+ | json_data | +-----------------+ | ["a","foo","d"] | +-----------------*/In the following example, the path$[1][0] is matched and replaces thearray element value withfoo.
SELECTJSON_SET(JSON'["a", ["b", "c"], "d"]','$[1][0]',"foo")ASjson_data/*-----------------------+ | json_data | +-----------------------+ | ["a",["foo","c"],"d"] | +-----------------------*/In the following example, the path prefix$ points to a JSON null, so theremainder of the path is created. The resulting array is padded withJSON nulls and appended withfoo.
SELECTJSON_SET(JSON'null','$[0][3]',"foo")/*--------------------------+ | json_data | +--------------------------+ | [[null,null,null,"foo"]] | +--------------------------*/In the following example, the path$[1] is matched, the matched array isextended since$[1][4] is larger than the existing array, and thenfoo isinserted in the array.
SELECTJSON_SET(JSON'["a", ["b", "c"], "d"]','$[1][4]',"foo")ASjson_data/*-------------------------------------+ | json_data | +-------------------------------------+ | ["a",["b","c",null,null,"foo"],"d"] | +-------------------------------------*/In the following example, the path$[1][0][0] implies that the value of$[1][0] is an array, but it isn't, so the operation is ignored.
SELECTJSON_SET(JSON'["a", ["b", "c"], "d"]','$[1][0][0]',"foo")ASjson_data/*---------------------+ | json_data | +---------------------+ | ["a",["b","c"],"d"] | +---------------------*/In the following example, the path$[1][2] is larger than the length ofthe matched array. The array length is extended and the remainder of the pathis recursively created. The operation continues to the path$[1][2][1]and insertsfoo.
SELECTJSON_SET(JSON'["a", ["b", "c"], "d"]','$[1][2][1]',"foo")ASjson_data/*----------------------------------+ | json_data | +----------------------------------+ | ["a",["b","c",[null,"foo"]],"d"] | +----------------------------------*/In the following example, because theJSON object is empty, keyb isinserted, and the remainder of the path is recursively created.
SELECTJSON_SET(JSON'{}','$.b[2].d',100)ASjson_data/*-----------------------------+ | json_data | +-----------------------------+ | {"b":[null,null,{"d":100}]} | +-----------------------------*/In the following example, multiple values are set.
SELECTJSON_SET(JSON'{"a": 1, "b": {"c":3}, "d": [4]}','$.a','v1','$.b.e','v2','$.d[2]','v3')ASjson_data/*---------------------------------------------------+ | json_data | +---------------------------------------------------+ | {"a":"v1","b":{"c":3,"e":"v2"},"d":[4,null,"v3"]} | +---------------------------------------------------*/JSON_STRIP_NULLS
JSON_STRIP_NULLS(json_expr[,json_path][,include_arrays=>{TRUE|FALSE}][,remove_empty=>{TRUE|FALSE}])Recursively removes JSON nulls from JSON objects and JSON arrays.
Arguments:
json_expr: JSON. For example:JSON'{"a": null, "b": "c"}'json_path: Remove JSON nulls at thisJSONPath forjson_expr.include_arrays: A named argument that's eitherTRUE(default) orFALSE. IfTRUEor omitted, the function removes JSON nulls from JSON arrays. IfFALSE, doesn't.remove_empty: A named argument that's eitherTRUEorFALSE(default). IfTRUE, the function removes empty JSON objects after JSON nulls are removed. IfFALSEor omitted, doesn't.If
remove_emptyisTRUEandinclude_arraysisTRUEor omitted,the function additionally removes empty JSON arrays.
Details:
- If a value is a JSON null, the associated key-value pair is removed.
- If
remove_emptyis set toTRUE, the function recursively removes emptycontainers after JSON nulls are removed. - If the function generates JSON with nothing in it, the function returns aJSON null.
- If
json_pathis an invalidJSONPath, an error isproduced. - If
json_expris SQLNULL, the function returns SQLNULL. - If
json_path,include_arrays, orremove_emptyis SQLNULL, thefunction returnsjson_expr.
Return type
JSON
Examples
In the following example, all JSON nulls are removed.
SELECTJSON_STRIP_NULLS(JSON'{"a": null, "b": "c"}')ASjson_data/*-----------+ | json_data | +-----------+ | {"b":"c"} | +-----------*/In the following example, all JSON nulls are removed from a JSON array.
SELECTJSON_STRIP_NULLS(JSON'[1, null, 2, null]')ASjson_data/*-----------+ | json_data | +-----------+ | [1,2] | +-----------*/In the following example,include_arrays is set asFALSE so that JSON nullsaren't removed from JSON arrays.
SELECTJSON_STRIP_NULLS(JSON'[1, null, 2, null]',include_arrays=>FALSE)ASjson_data/*-----------------+ | json_data | +-----------------+ | [1,null,2,null] | +-----------------*/In the following example,remove_empty is omitted and defaults toFALSE, and the empty structures are retained.
SELECTJSON_STRIP_NULLS(JSON'[1, null, 2, null, [null]]')ASjson_data/*-----------+ | json_data | +-----------+ | [1,2,[]] | +-----------*/In the following example,remove_empty is set asTRUE, and theempty structures are removed.
SELECTJSON_STRIP_NULLS(JSON'[1, null, 2, null, [null]]',remove_empty=>TRUE)ASjson_data/*-----------+ | json_data | +-----------+ | [1,2] | +-----------*/In the following examples,remove_empty is set asTRUE, and theempty structures are removed. Because no JSON data is left the functionreturns JSON null.
SELECTJSON_STRIP_NULLS(JSON'{"a": null}',remove_empty=>TRUE)ASjson_data/*-----------+ | json_data | +-----------+ | null | +-----------*/SELECTJSON_STRIP_NULLS(JSON'{"a": [null]}',remove_empty=>TRUE)ASjson_data/*-----------+ | json_data | +-----------+ | null | +-----------*/In the following example, empty structures are removed for JSON objects,but not JSON arrays.
SELECTJSON_STRIP_NULLS(JSON'{"a": {"b": {"c": null}}, "d": [null], "e": [], "f": 1}',include_arrays=>FALSE,remove_empty=>TRUE)ASjson_data/*---------------------------+ | json_data | +---------------------------+ | {"d":[null],"e":[],"f":1} | +---------------------------*/In the following example, empty structures are removed for both JSON objects,and JSON arrays.
SELECTJSON_STRIP_NULLS(JSON'{"a": {"b": {"c": null}}, "d": [null], "e": [], "f": 1}',remove_empty=>TRUE)ASjson_data/*-----------+ | json_data | +-----------+ | {"f":1} | +-----------*/In the following example, because no JSON data is left, the function returns aJSON null.
SELECTJSON_STRIP_NULLS(JSON'null')ASjson_data/*-----------+ | json_data | +-----------+ | null | +-----------*/JSON_TYPE
JSON_TYPE(json_expr)Description
Gets the JSON type of the outermost JSON value and converts the name ofthis type to a SQLSTRING value. The names of these JSON types can bereturned:object,array,string,number,boolean,null
Arguments:
json_expr: JSON. For example:JSON'{"name": "sky", "color": "blue"}'If this expression is SQL
NULL, the function returns SQLNULL. If theextracted JSON value isn't a valid JSON type, an error is produced.
Return type
STRING
Examples
SELECTjson_val,JSON_TYPE(json_val)AStypeFROMUNNEST([JSON'"apple"',JSON'10',JSON'3.14',JSON'null',JSON'{"city": "New York", "State": "NY"}',JSON'["apple", "banana"]',JSON'false'])ASjson_val;/*----------------------------------+---------+ | json_val | type | +----------------------------------+---------+ | "apple" | string | | 10 | number | | 3.14 | number | | null | null | | {"State":"NY","city":"New York"} | object | | ["apple","banana"] | array | | false | boolean | +----------------------------------+---------*/JSON_VALUE
JSON_VALUE(json_string_expr[,json_path])JSON_VALUE(json_expr[,json_path])Description
Extracts a JSON scalar value and converts it to a SQLSTRING value.In addition, this function:
- Removes the outermost quotes and unescapes the values.
- Returns a SQL
NULLif a non-scalar value is selected. - Uses double quotes to escape invalidJSONPath charactersin JSON keys. For example:
"a.b".
Arguments:
json_string_expr: A JSON-formatted string. For example:'{"name": "Jakob", "age": "6"}'json_expr: JSON. For example:JSON'{"name": "Jane", "age": "6"}'json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input. If this optional parameter isn'tprovided, then the JSONPath$symbol is applied, which means that all ofthe data is analyzed.If
json_pathreturns a JSONnullor a non-scalar value (in other words,ifjson_pathrefers to an object or an array), then a SQLNULLisreturned.
There are differences between the JSON-formatted string and JSON input types.For details, seeDifferences between the JSON and JSON-formatted STRING types.
Return type
STRING
Examples
In the following example, JSON data is extracted and returned as a scalar value.
SELECTJSON_VALUE(JSON'{"name": "Jakob", "age": "6" }','$.age')ASscalar_age;/*------------+ | scalar_age | +------------+ | 6 | +------------*/The following example compares how results are returned for theJSON_QUERYandJSON_VALUE functions.
SELECTJSON_QUERY('{"name": "Jakob", "age": "6"}','$.name')ASjson_name,JSON_VALUE('{"name": "Jakob", "age": "6"}','$.name')ASscalar_name,JSON_QUERY('{"name": "Jakob", "age": "6"}','$.age')ASjson_age,JSON_VALUE('{"name": "Jakob", "age": "6"}','$.age')ASscalar_age;/*-----------+-------------+----------+------------+ | json_name | scalar_name | json_age | scalar_age | +-----------+-------------+----------+------------+ | "Jakob" | Jakob | "6" | 6 | +-----------+-------------+----------+------------*/SELECTJSON_QUERY('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_query,JSON_VALUE('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_value;/*--------------------+------------+ | json_query | json_value | +--------------------+------------+ | ["apple","banana"] | NULL | +--------------------+------------*/In cases where a JSON key uses invalid JSONPath characters, you can escape thosecharacters using double quotes. For example:
SELECTJSON_VALUE('{"a.b": {"c": "world"}}','$."a.b".c')AShello;/*-------+ | hello | +-------+ | world | +-------*/JSON_VALUE_ARRAY
JSON_VALUE_ARRAY(json_string_expr[,json_path])JSON_VALUE_ARRAY(json_expr[,json_path])Description
Extracts a JSON array of scalar values and converts it to a SQLARRAY<STRING> value.In addition, this function:
- Removes the outermost quotes and unescapes the values.
- Returns a SQL
NULLif the selected value isn't an array ornot an array containing only scalar values. - Uses double quotes to escape invalidJSONPath charactersin JSON keys. For example:
"a.b".
Arguments:
json_string_expr: A JSON-formatted string. For example:'["apples", "oranges", "grapes"]'json_expr: JSON. For example:JSON'["apples", "oranges", "grapes"]'json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input. If this optional parameter isn'tprovided, then the JSONPath$symbol is applied, which means that all ofthe data is analyzed.
There are differences between the JSON-formatted string and JSON input types.For details, seeDifferences between the JSON and JSON-formatted STRING types.
Caveats:
- A JSON
nullin the input array produces a SQLNULLas the output for thatJSONnull. - If a JSONPath matches an array that contains scalar objects and a JSON
null,then the output is an array of the scalar objects and a SQLNULL.
Return type
ARRAY<STRING>
Examples
This extracts items in JSON to a string array:
SELECTJSON_VALUE_ARRAY(JSON'{"fruits": ["apples", "oranges", "grapes"]}','$.fruits')ASstring_array;/*---------------------------+ | string_array | +---------------------------+ | [apples, oranges, grapes] | +---------------------------*/The following example compares how results are returned for theJSON_QUERY_ARRAY andJSON_VALUE_ARRAY functions.
SELECTJSON_QUERY_ARRAY('["apples", "oranges"]')ASjson_array,JSON_VALUE_ARRAY('["apples", "oranges"]')ASstring_array;/*-----------------------+-------------------+ | json_array | string_array | +-----------------------+-------------------+ | ["apples", "oranges"] | [apples, oranges] | +-----------------------+-------------------*/This extracts the items in a JSON-formatted string to a string array:
-- Strips the double quotesSELECTJSON_VALUE_ARRAY('["foo", "bar", "baz"]','$')ASstring_array;/*-----------------+ | string_array | +-----------------+ | [foo, bar, baz] | +-----------------*/This extracts a string array and converts it to an integer array:
SELECTARRAY(SELECTCAST(integer_elementASINT64)FROMUNNEST(JSON_VALUE_ARRAY('[1, 2, 3]','$'))ASinteger_element)ASinteger_array;/*---------------+ | integer_array | +---------------+ | [1, 2, 3] | +---------------*/These are equivalent:
SELECTJSON_VALUE_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$.fruits')ASstring_array;SELECTJSON_VALUE_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$."fruits"')ASstring_array;-- The queries above produce the following result:/*---------------------------+ | string_array | +---------------------------+ | [apples, oranges, grapes] | +---------------------------*/In cases where a JSON key uses invalid JSONPath characters, you can escape thosecharacters using double quotes:" ". For example:
SELECTJSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}','$."a.b".c')AShello;/*---------+ | hello | +---------+ | [world] | +---------*/The following examples explore how invalid requests and empty arrays arehandled:
-- An error is thrown if you provide an invalid JSONPath.SELECTJSON_VALUE_ARRAY('["foo", "bar", "baz"]','INVALID_JSONPath')ASresult;-- If the JSON-formatted string is invalid, then NULL is returned.SELECTJSON_VALUE_ARRAY('}}','$')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If the JSON document is NULL, then NULL is returned.SELECTJSON_VALUE_ARRAY(NULL,'$')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If a JSONPath doesn't match anything, then the output is NULL.SELECTJSON_VALUE_ARRAY('{"a": ["foo", "bar", "baz"]}','$.b')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If a JSONPath matches an object that isn't an array, then the output is NULL.SELECTJSON_VALUE_ARRAY('{"a": "foo"}','$')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.SELECTJSON_VALUE_ARRAY('{"a": [{"b": "foo", "c": 1}, {"b": "bar", "c": 2}], "d": "baz"}','$.a')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If a JSONPath matches an array of mixed scalar and non-scalar objects,-- then the output is NULL.SELECTJSON_VALUE_ARRAY('{"a": [10, {"b": 20}]','$.a')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.SELECTJSON_VALUE_ARRAY('{"a": "foo", "b": []}','$.b')ASresult;/*--------+ | result | +--------+ | [] | +--------*/-- In the following query, the JSON null input is returned as a-- SQL NULL in the output.SELECTJSON_VALUE_ARRAY('["world", null, 1]')ASresult;/*------------------+ | result | +------------------+ | [world, NULL, 1] | +------------------*/LAX_BOOL
LAX_BOOL(json_expr)Description
Attempts to convert a JSON value to a SQLBOOL value.
Arguments:
json_expr: JSON. For example:JSON'true'
Details:
- If
json_expris SQLNULL, the function returns SQLNULL. - See the conversion rules in the next section for additional
NULLhandling.
Conversion rules
| From JSON type | To SQLBOOL |
|---|---|
| boolean | If the JSON boolean istrue, returnsTRUE. Otherwise, returnsFALSE. |
| string | If the JSON string is'true', returnsTRUE. If the JSON string is'false', returnsFALSE. If the JSON string is any other value or has whitespace in it, returnsNULL. This conversion is case-insensitive. |
| number | If the JSON number is a representation of0, returnsFALSE. Otherwise, returnsTRUE. |
| other type or null | NULL |
Return type
BOOL
Examples
Example with input that's a JSON boolean:
SELECTLAX_BOOL(JSON'true')ASresult;/*--------+ | result | +--------+ | true | +--------*/Examples with inputs that are JSON strings:
SELECTLAX_BOOL(JSON'"true"')ASresult;/*--------+ | result | +--------+ | TRUE | +--------*/SELECTLAX_BOOL(JSON'"true "')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/SELECTLAX_BOOL(JSON'"foo"')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/Examples with inputs that are JSON numbers:
SELECTLAX_BOOL(JSON'10')ASresult;/*--------+ | result | +--------+ | TRUE | +--------*/SELECTLAX_BOOL(JSON'0')ASresult;/*--------+ | result | +--------+ | FALSE | +--------*/SELECTLAX_BOOL(JSON'0.0')ASresult;/*--------+ | result | +--------+ | FALSE | +--------*/SELECTLAX_BOOL(JSON'-1.1')ASresult;/*--------+ | result | +--------+ | TRUE | +--------*/LAX_FLOAT64
LAX_FLOAT64(json_expr)Description
Attempts to convert a JSON value to aSQLFLOAT64 value.
Arguments:
json_expr: JSON. For example:JSON'9.8'
Details:
- If
json_expris SQLNULL, the function returns SQLNULL. - See the conversion rules in the next section for additional
NULLhandling.
Conversion rules
| From JSON type | To SQLFLOAT64 |
|---|---|
| boolean | NULL |
| string | If the JSON string represents a JSON number, parses it as a JSON number, and then safe casts the result as aFLOAT64 value. If the JSON string can't be converted, returnsNULL. |
| number | Casts the JSON number as aFLOAT64 value. Large JSON numbers are rounded. |
| other type or null | NULL |
Return type
FLOAT64
Examples
Examples with inputs that are JSON numbers:
SELECTLAX_FLOAT64(JSON'9.8')ASresult;/*--------+ | result | +--------+ | 9.8 | +--------*/SELECTLAX_FLOAT64(JSON'9')ASresult;/*--------+ | result | +--------+ | 9.0 | +--------*/SELECTLAX_FLOAT64(JSON'9007199254740993')ASresult;/*--------------------+ | result | +--------------------+ | 9007199254740992.0 | +--------------------*/SELECTLAX_FLOAT64(JSON'1e100')ASresult;/*--------+ | result | +--------+ | 1e+100 | +--------*/Examples with inputs that are JSON booleans:
SELECTLAX_FLOAT64(JSON'true')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/SELECTLAX_FLOAT64(JSON'false')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/Examples with inputs that are JSON strings:
SELECTLAX_FLOAT64(JSON'"10"')ASresult;/*--------+ | result | +--------+ | 10.0 | +--------*/SELECTLAX_FLOAT64(JSON'"1.1"')ASresult;/*--------+ | result | +--------+ | 1.1 | +--------*/SELECTLAX_FLOAT64(JSON'"1.1e2"')ASresult;/*--------+ | result | +--------+ | 110.0 | +--------*/SELECTLAX_FLOAT64(JSON'"9007199254740993"')ASresult;/*--------------------+ | result | +--------------------+ | 9007199254740992.0 | +--------------------*/SELECTLAX_FLOAT64(JSON'"+1.5"')ASresult;/*--------+ | result | +--------+ | 1.5 | +--------*/SELECTLAX_FLOAT64(JSON'"NaN"')ASresult;/*--------+ | result | +--------+ | NaN | +--------*/SELECTLAX_FLOAT64(JSON'"Inf"')ASresult;/*----------+ | result | +----------+ | Infinity | +----------*/SELECTLAX_FLOAT64(JSON'"-InfiNiTY"')ASresult;/*-----------+ | result | +-----------+ | -Infinity | +-----------*/SELECTLAX_FLOAT64(JSON'"foo"')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/LAX_INT64
LAX_INT64(json_expr)Description
Attempts to convert a JSON value to a SQLINT64 value.
Arguments:
json_expr: JSON. For example:JSON'999'
Details:
- If
json_expris SQLNULL, the function returns SQLNULL. - See the conversion rules in the next section for additional
NULLhandling.
Conversion rules
| From JSON type | To SQLINT64 |
|---|---|
| boolean | If the JSON boolean istrue, returns1. Iffalse, returns0. |
| string | If the JSON string represents a JSON number, parses it as a JSON number, and then safe casts the results as anINT64 value. If the JSON string can't be converted, returnsNULL. |
| number | Casts the JSON number as anINT64 value. If the JSON number can't be converted, returnsNULL. |
| other type or null | NULL |
Return type
INT64
Examples
Examples with inputs that are JSON numbers:
SELECTLAX_INT64(JSON'10')ASresult;/*--------+ | result | +--------+ | 10 | +--------*/SELECTLAX_INT64(JSON'10.0')ASresult;/*--------+ | result | +--------+ | 10 | +--------*/SELECTLAX_INT64(JSON'1.1')ASresult;/*--------+ | result | +--------+ | 1 | +--------*/SELECTLAX_INT64(JSON'3.5')ASresult;/*--------+ | result | +--------+ | 4 | +--------*/SELECTLAX_INT64(JSON'1.1e2')ASresult;/*--------+ | result | +--------+ | 110 | +--------*/SELECTLAX_INT64(JSON'1e100')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/Examples with inputs that are JSON booleans:
SELECTLAX_INT64(JSON'true')ASresult;/*--------+ | result | +--------+ | 1 | +--------*/SELECTLAX_INT64(JSON'false')ASresult;/*--------+ | result | +--------+ | 0 | +--------*/Examples with inputs that are JSON strings:
SELECTLAX_INT64(JSON'"10"')ASresult;/*--------+ | result | +--------+ | 10 | +--------*/SELECTLAX_INT64(JSON'"1.1"')ASresult;/*--------+ | result | +--------+ | 1 | +--------*/SELECTLAX_INT64(JSON'"1.1e2"')ASresult;/*--------+ | result | +--------+ | 110 | +--------*/SELECTLAX_INT64(JSON'"+1.5"')ASresult;/*--------+ | result | +--------+ | 2 | +--------*/SELECTLAX_INT64(JSON'"1e100"')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/SELECTLAX_INT64(JSON'"foo"')ASresult;/*--------+ | result | +--------+ | NULL | +--------*/LAX_STRING
LAX_STRING(json_expr)Description
Attempts to convert a JSON value to a SQLSTRING value.
Arguments:
json_expr: JSON. For example:JSON'"name"'
Details:
- If
json_expris SQLNULL, the function returns SQLNULL. - See the conversion rules in the next section for additional
NULLhandling.
Conversion rules
| From JSON type | To SQLSTRING |
|---|---|
| boolean | If the JSON boolean istrue, returns'true'. Iffalse, returns'false'. |
| string | Returns the JSON string as aSTRING value. |
| number | Returns the JSON number as aSTRING value. |
| other type or null | NULL |
Return type
STRING
Examples
Examples with inputs that are JSON strings:
SELECTLAX_STRING(JSON'"purple"')ASresult;/*--------+ | result | +--------+ | purple | +--------*/SELECTLAX_STRING(JSON'"10"')ASresult;/*--------+ | result | +--------+ | 10 | +--------*/Examples with inputs that are JSON booleans:
SELECTLAX_STRING(JSON'true')ASresult;/*--------+ | result | +--------+ | true | +--------*/SELECTLAX_STRING(JSON'false')ASresult;/*--------+ | result | +--------+ | false | +--------*/Examples with inputs that are JSON numbers:
SELECTLAX_STRING(JSON'10.0')ASresult;/*--------+ | result | +--------+ | 10 | +--------*/SELECTLAX_STRING(JSON'10')ASresult;/*--------+ | result | +--------+ | 10 | +--------*/SELECTLAX_STRING(JSON'1e100')ASresult;/*--------+ | result | +--------+ | 1e+100 | +--------*/PARSE_JSON
PARSE_JSON(json_string_expr[,wide_number_mode=>{'exact'|'round'}])Description
Converts a JSON-formattedSTRING value to aJSON value.
Arguments:
json_string_expr: A JSON-formatted string. For example:'{"class": {"students": [{"name": "Jane"}]}}'wide_number_mode: A named argument with aSTRINGvalue. Determineshow to handle numbers that can't be stored in aJSONvalue without theloss of precision. If used,wide_number_modemust include one of thefollowing values:exact(default): Only accept numbers that can be stored without lossof precision. If a number that can't be stored without loss ofprecision is encountered, the function throws an error.round: If a number that can't be stored without loss of precision isencountered, attempt to round it to a number that can be stored withoutloss of precision. If the number can't be rounded, the function throwsan error.
If a number appears in a JSON object or array, the
wide_number_modeargument is applied to the number in the object or array.
Numbers from the following domains can be stored in JSON without loss ofprecision:
- 64-bit signed/unsigned integers, such as
INT64 FLOAT64
Return type
JSON
Examples
In the following example, a JSON-formatted string is converted toJSON.
SELECTPARSE_JSON('{"coordinates": [10, 20], "id": 1}')ASjson_data;/*--------------------------------+ | json_data | +--------------------------------+ | {"coordinates":[10,20],"id":1} | +--------------------------------*/The following queries fail because:
- The number that was passed in can't be stored without loss of precision.
wide_number_mode=>'exact'is used implicitly in the first query andexplicitly in the second query.
SELECTPARSE_JSON('{"id": 922337203685477580701}')ASjson_data;-- failsSELECTPARSE_JSON('{"id": 922337203685477580701}',wide_number_mode=>'exact')ASjson_data;-- failsThe following query rounds the number to a number that can be stored in JSON.
SELECTPARSE_JSON('{"id": 922337203685477580701}',wide_number_mode=>'round')ASjson_data;/*------------------------------+ | json_data | +------------------------------+ | {"id":9.223372036854776e+20} | +------------------------------*/You can also use valid JSON-formatted strings that don't represent name/value pairs. For example:
SELECTPARSE_JSON('6')ASjson_data;/*------------------------------+ | json_data | +------------------------------+ | 6 | +------------------------------*/SELECTPARSE_JSON('"red"')ASjson_data;/*------------------------------+ | json_data | +------------------------------+ | "red" | +------------------------------*/SAFE_TO_JSON
SAFE_TO_JSON(sql_value)Description
Similar to theTO_JSON function, but for each unsupported field in theinput argument, produces a JSON null instead of an error.
Arguments:
sql_value: The SQL value to convert to a JSON value. You can review theGoogleSQL data types that this function supports and theirJSON encodings.
Return type
JSON
Example
The following queries are functionally the same, except thatSAFE_TO_JSONproduces a JSON null instead of an error when a hypothetical unsupporteddata type is encountered:
-- Produces a JSON null.SELECTSAFE_TO_JSON(CAST(b''ASUNSUPPORTED_TYPE))asresult;-- Produces an error.SELECTTO_JSON(CAST(b''ASUNSUPPORTED_TYPE),stringify_wide_numbers=>TRUE)asresult;In the following query, the value forut is ignored because the value is anunsupported type:
SELECTSAFE_TO_JSON(STRUCT(CAST(b''ASUNSUPPORTED_TYPE)ASut)ASresult;/*--------------+ | result | +--------------+ | {"ut": null} | +--------------*/The following array produces a JSON null instead of an error because the datatype for the array isn't supported.
SELECTSAFE_TO_JSON([CAST(b''ASUNSUPPORTED_TYPE),CAST(b''ASUNSUPPORTED_TYPE),CAST(b''ASUNSUPPORTED_TYPE),])ASresult;/*------------+ | result | +------------+ | null | +------------*/STRING
STRING(json_expr)Description
Converts a JSON string to a SQLSTRING value.
Arguments:
json_expr: JSON. For example:JSON'"purple"'If the JSON value isn't a string, an error is produced. If the expressionis SQL
NULL, the function returns SQLNULL.
Return type
STRING
Examples
SELECTSTRING(JSON'"purple"')AScolor;/*--------+ | color | +--------+ | purple | +--------*/SELECTSTRING(JSON_QUERY(JSON'{"name": "sky", "color": "blue"}',"$.color"))AScolor;/*-------+ | color | +-------+ | blue | +-------*/The following examples show how invalid requests are handled:
-- An error is thrown if the JSON isn't of type string.SELECTSTRING(JSON'123')ASresult;-- Throws an errorSELECTSTRING(JSON'null')ASresult;-- Throws an errorSELECTSAFE.STRING(JSON'123')ASresult;-- Returns a SQL NULLSTRING_ARRAY
STRING_ARRAY(json_expr)Description
Converts a JSON array of strings to a SQLARRAY<STRING> value.
Arguments:
json_expr: JSON. For example:JSON'["purple", "blue"]'If the JSON value isn't an array of strings, an error is produced. If theexpression is SQL
NULL, the function returns SQLNULL.
Return type
ARRAY<STRING>
Examples
SELECTSTRING_ARRAY(JSON'["purple", "blue"]')AScolors;/*----------------+ | colors | +----------------+ | [purple, blue] | +----------------*/The following examples show how invalid requests are handled:
-- An error is thrown if the JSON isn't an array of strings.SELECTSTRING_ARRAY(JSON'[123]')ASresult;-- Throws an errorSELECTSTRING_ARRAY(JSON'[null]')ASresult;-- Throws an errorSELECTSTRING_ARRAY(JSON'null')ASresult;-- Throws an errorTO_JSON
TO_JSON(sql_value[,stringify_wide_numbers=>{TRUE|FALSE}])Description
Converts a SQL value to a JSON value.
Arguments:
sql_value: The SQL value to convert to a JSON value. You can review theGoogleSQL data types that this function supports and theirJSON encodingshere.stringify_wide_numbers: A named argument that's eitherTRUEorFALSE(default).- If
TRUE, numeric values outside of theFLOAT64type domain are encoded as strings. - If
FALSE(default), numeric values outside of theFLOAT64type domain aren't encoded as strings,but are stored as JSON numbers. If a numerical value can't be stored inJSON without loss of precision, an error is thrown.
The following numerical data types are affected by the
stringify_wide_numbersargument:- If
INT64NUMERICIf one of these numerical data types appears in a container data typesuch as an
ARRAYorSTRUCT, thestringify_wide_numbersargument isapplied to the numerical data types in the container data type.
Return type
JSON
Examples
In the following example, the query converts rows in a table to JSON values.
WithCoordinatesTableAS((SELECT1ASid,[10,20]AScoordinates)UNIONALL(SELECT2ASid,[30,40]AScoordinates)UNIONALL(SELECT3ASid,[50,60]AScoordinates))SELECTTO_JSON(t)ASjson_objectsFROMCoordinatesTableASt;/*--------------------------------+ | json_objects | +--------------------------------+ | {"coordinates":[10,20],"id":1} | | {"coordinates":[30,40],"id":2} | | {"coordinates":[50,60],"id":3} | +--------------------------------*/In the following example, the query returns a large numerical value as aJSON string.
SELECTTO_JSON(9007199254740993,stringify_wide_numbers=>TRUE)asstringify_on;/*--------------------+ | stringify_on | +--------------------+ | "9007199254740993" | +--------------------*/In the following example, both queries return a large numerical value as aJSON number.
SELECTTO_JSON(9007199254740993,stringify_wide_numbers=>FALSE)asstringify_off;SELECTTO_JSON(9007199254740993)asstringify_off;/*------------------+ | stringify_off | +------------------+ | 9007199254740993 | +------------------*/In the following example, only large numeric values are converted toJSON strings.
WithT1AS((SELECT9007199254740993ASid)UNIONALL(SELECT2ASid))SELECTTO_JSON(t,stringify_wide_numbers=>TRUE)ASjson_objectsFROMT1ASt;/*---------------------------+ | json_objects | +---------------------------+ | {"id":"9007199254740993"} | | {"id":2} | +---------------------------*/In this example, the values9007199254740993 (INT64)and2.1 (FLOAT64) are convertedto the common supertypeFLOAT64, which isn'taffected by thestringify_wide_numbers argument.
WithT1AS((SELECT9007199254740993ASid)UNIONALL(SELECT2.1ASid))SELECTTO_JSON(t,stringify_wide_numbers=>TRUE)ASjson_objectsFROMT1ASt;/*------------------------------+ | json_objects | +------------------------------+ | {"id":9.007199254740992e+15} | | {"id":2.1} | +------------------------------*/In the following example, a graph path is converted into a JSON array.
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(dst:Account)RETURNTO_JSON(p)ASjson_array/*--------------------------------------------------------------------+ | json_array | +--------------------------------------------------------------------+ | [{ | | "identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEg", | | "kind":"node", | | "labels":["Account"], | | "properties":{ | | "create_time":"2020-01-28T01:55:09.206Z", | | "id":16, | | "is_blocked":true, | | "nick_name":"Vacation Fund" | | } | | }, | | { | | "destination_node_identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEo", | | "identifier":"mUZpbkdyYXBoLkFjY291...", | | "kind":"edge", | | "labels":["Transfers"], | | "properties":{ | | "amount":300.0, | | "create_time":"2020-09-25T09:36:14.926Z", | | "id":16, | | "order_number":"103650009791820", | | "to_id":20 | | }, | | "source_node_identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEg" | | }, | | { | | "identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEo", | | "kind":"node", | | "labels":["Account"], | | "properties":{ | | "create_time":"2020-02-18T13:44:20.655Z", | | "id":20, | | "is_blocked":false, | | "nick_name":"Vacation Fund" | | } | | } | | ... | | ] | +--------------------------------------------------------------------/*TO_JSON_STRING
TO_JSON_STRING(json_expr)Description
Converts a JSON value to a SQL JSON-formattedSTRING value.
Arguments:
json_expr: JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'
Return type
A JSON-formattedSTRING
Example
Convert a JSON value to a JSON-formattedSTRING value.
SELECTTO_JSON_STRING(JSON'{"id": 1, "coordinates": [10, 20]}')ASjson_string/*--------------------------------+ | json_string | +--------------------------------+ | {"coordinates":[10,20],"id":1} | +--------------------------------*/Supplemental materials
Differences between the JSON and JSON-formatted STRING types
Many JSON functions accept two input types:
JSONtypeSTRINGtype
TheSTRING version of the extraction functions behaves differently than theJSON version, mainly becauseJSON type values are always validated whereasJSON-formattedSTRING type values aren't.
Non-validation ofSTRING inputs
The followingSTRING is invalid JSON because it's missing a trailing}:
{"hello":"world"The JSON function reads the input from the beginning and stops as soon as thefield to extract is found, without reading the remainder of the input. A parsingerror isn't produced.
With theJSON type, however,JSON '{"hello": "world"' returns a parsingerror.
For example:
SELECTJSON_VALUE('{"hello": "world"',"$.hello")AShello;/*-------+ | hello | +-------+ | world | +-------*/SELECTJSON_VALUE(JSON'{"hello": "world"',"$.hello")AShello;-- An error is returned: Invalid JSON literal: syntax error while parsing-- object - unexpected end of input; expected '}'No strict validation of extracted values
In the following examples, duplicated keys aren't removed when using aJSON-formatted string. Similarly, keys order is preserved. For theJSONtype,JSON '{"key": 1, "key": 2}' will result inJSON '{"key":1}' duringparsing.
SELECTJSON_QUERY('{"key": 1, "key": 2}',"$")ASstring;/*-------------------+ | string | +-------------------+ | {"key":1,"key":2} | +-------------------*/SELECTJSON_QUERY(JSON'{"key": 1, "key": 2}',"$")ASjson;/*-----------+ | json | +-----------+ | {"key":1} | +-----------*/JSONnull
When using a JSON-formattedSTRING type in a JSON function, a JSONnullvalue is extracted as a SQLNULL value.
When using a JSON type in a JSON function, a JSONnull value returns a JSONnull value.
WITHtAS(SELECT'{"name": null}'ASjson_string,JSON'{"name": null}'ASjson)SELECTJSON_QUERY(json_string,"$.name")ASname_string,JSON_QUERY(json_string,"$.name")ISNULLASname_string_is_null,JSON_QUERY(json,"$.name")ASname_json,JSON_QUERY(json,"$.name")ISNULLASname_json_is_nullFROMt;/*-------------+---------------------+-----------+-------------------+ | name_string | name_string_is_null | name_json | name_json_is_null | +-------------+---------------------+-----------+-------------------+ | NULL | true | null | false | +-------------+---------------------+-----------+-------------------*/JSON encodings
You can encode a SQL value as a JSON value with the following functions:
TO_JSONJSON_SET(usesTO_JSONencoding)JSON_ARRAY(usesTO_JSONencoding)JSON_ARRAY_APPEND(usesTO_JSONencoding)JSON_ARRAY_INSERT(usesTO_JSONencoding)JSON_OBJECT(usesTO_JSONencoding)
The following SQL to JSON encodings are supported:
| From SQL | To JSON | Examples |
|---|---|---|
| NULL | null | SQL input:NULLJSON output: null |
| BOOL | boolean | SQL input:TRUEJSON output: trueSQL input: FALSEJSON output: false |
| INT64 | number or string If the If the | SQL input:9007199254740992JSON output: 9007199254740992SQL input: 9007199254740993JSON output: 9007199254740993SQL input with stringify_wide_numbers=>TRUE: 9007199254740992JSON output: 9007199254740992SQL input with stringify_wide_numbers=>TRUE: 9007199254740993JSON output: "9007199254740993" |
| INTERVAL | string | SQL input:INTERVAL '10:20:30.52' HOUR TO SECONDJSON output: "PT10H20M30.52S"SQL input: INTERVAL 1 SECONDJSON output: "PT1S"INTERVAL -25 MONTHJSON output: "P-2Y-1M"INTERVAL '1 5:30' DAY TO MINUTEJSON output: "P1DT5H30M" |
| NUMERIC | number or string If the | SQL input:-1JSON output: -1SQL input: 0JSON output: 0SQL input: 9007199254740993JSON output: 9007199254740993SQL input: 123.56JSON output: 123.56SQL input with stringify_wide_numbers=>TRUE: 9007199254740993JSON output: "9007199254740993"SQL input with stringify_wide_numbers=>TRUE: 123.56JSON output: 123.56 |
| FLOAT64 | number or string
| SQL input:1.0JSON output: 1SQL input: 9007199254740993JSON output: 9007199254740993SQL input: "+inf"JSON output: "Infinity"SQL input: "-inf"JSON output: "-Infinity"SQL input: "NaN"JSON output: "NaN" |
| STRING | string Encoded as a string, escaped according to the JSON standard. Specifically, | SQL input:"abc"JSON output: "abc"SQL input: "\"abc\""JSON output: "\"abc\"" |
| BYTES | string Uses RFC 4648 Base64 data encoding. | SQL input:b"Google"JSON output: "R29vZ2xl" |
| ENUM | string Invalid enum values are encoded as their number, such as 0 or 42. | SQL input:Color.RedJSON output: "Red" |
| DATE | string | SQL input:DATE '2017-03-06'JSON output: "2017-03-06" |
| TIMESTAMP | string Encoded as ISO 8601 date and time, where T separates the date and time and Z (Zulu/UTC) represents the time zone. | SQL input:TIMESTAMP '2017-03-06 12:34:56.789012'JSON output: "2017-03-06T12:34:56.789012Z" |
| JSON | data of the input JSON | SQL input:JSON '{"item": "pen", "price": 10}'JSON output: {"item":"pen", "price":10}SQL input: [1, 2, 3]JSON output: [1, 2, 3] |
| ARRAY | array Can contain zero or more elements. | SQL input:["red", "blue", "green"]JSON output: ["red","blue","green"]SQL input: [1, 2, 3]JSON output: [1,2,3] |
| STRUCT | object The object can contain zero or more key-value pairs. Each value is formatted according to its type. For Anonymous fields are represented with Invalid UTF-8 field names might result in unparseable JSON. String values are escaped according to the JSON standard. Specifically, | SQL input:STRUCT(12 AS purchases, TRUE AS inStock)JSON output: {"inStock": true,"purchases":12} |
| PROTO | object The object can contain zero or more key-value pairs. Each value is formatted according to its type. Field names with underscores are converted to camel case in accordance with protobuf json conversion. Field values are formatted according to protobuf json conversion. If a
| SQL input:NEW Item(12 AS purchases,TRUE AS in_Stock)JSON output: {"purchases":12,"inStock": true} |
| GRAPH_ELEMENT | object The object can contain zero or more key-value pairs. Each value is formatted according to its type. For
| SQL:GRAPH FinGraphMATCH (p:Person WHERE p.name = 'Dana')RETURN TO_JSON(p) AS dana_json; JSON output (truncated): {"identifier":"ZGFuYQ==","kind":"node","labels":["Person"],"properties":{"id":2,"name":"Dana"}} |
| GRAPH_PATH | array The array can contain one or more objects that represent graph elements in a graph path. | SQL:GRAPH FinGraphMATCH account_ownership = (p:Person)-[o:Owns]->(a:Account)RETURN TO_JSON(account_ownership) AS results JSON output for account_ownership (truncated):[ {"identifier":"ZGFuYQ==","kind":"node","labels":["Person"], ...}, {"identifier":"TPZuYM==","kind":"edge","labels":["Owns"], ...}, {"identifier":"PRTuMI==","kind":"node","labels":["Account"], ...}] |
JSONPath format
With the JSONPath format, you can identify the values you want toobtain from a JSON-formatted string.
If a key in a JSON functions contains a JSON format operator, refer to eachJSON function for how to escape them.
A JSON function returnsNULL if the JSONPath format doesn't match a value ina JSON-formatted string. If the selected value for a scalar function isn'tscalar, such as an object or an array, the function returnsNULL. If theJSONPath format is invalid, an error is produced.
Operators for JSONPath
The JSONPath format supports these operators:
| Operator | Description | Examples |
|---|---|---|
$ | Root object or element. The JSONPath format must start with this operator, which refers to the outermost level of the JSON-formatted string. | JSON-formatted string: JSON path: JSON result: |
. | Child operator. You can identify child values using dot-notation. | JSON-formatted string: JSON path: JSON result: |
[] | Subscript operator. If the object is a JSON array, you can use brackets to specify the array index. | JSON-formatted string: JSON path: JSON result: |
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.