JSON functions
GoogleSQL for BigQuery 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 |
| Standard extractors | JSON_QUERYJSON_VALUEJSON_QUERY_ARRAYJSON_VALUE_ARRAY | Functions that extract JSON data. |
| Legacy extractors | JSON_EXTRACTJSON_EXTRACT_SCALARJSON_EXTRACT_ARRAYJSON_EXTRACT_STRING_ARRAY | Functions that extract JSON data. While these functions are supported by GoogleSQL, we recommend using thestandard extractor functions. |
| Lax converters | LAX_BOOLLAX_FLOAT64LAX_INT64LAX_STRING | Functions that flexibly convert a JSON value to a SQL value without returning errors. |
| Converters | BOOLFLOAT64INT64STRING | Functions that convert a JSON value to a SQL value. |
| Other converters | PARSE_JSONTO_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. |
Function list
| Name | Summary |
|---|---|
BOOL | Converts a JSON boolean to a SQLBOOL value. |
FLOAT64 | Converts a JSON number to a SQLFLOAT64 value. |
INT64 | Converts a JSON number to a SQLINT64 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_EXTRACT | (Deprecated) Extracts a JSON value and converts it to a SQL JSON-formattedSTRING orJSON value. |
JSON_EXTRACT_ARRAY | (Deprecated) Extracts a JSON array and converts it to a SQLARRAY<JSON-formatted STRING> orARRAY<JSON> value. |
JSON_EXTRACT_SCALAR | (Deprecated) Extracts a JSON scalar value and converts it to a SQLSTRING value. |
JSON_EXTRACT_STRING_ARRAY | (Deprecated) Extracts a JSON array of scalar values and converts it to a SQLARRAY<STRING> value. |
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. |
STRING (JSON) | Converts a JSON string to a SQLSTRING value. |
TO_JSON | Converts a SQL value to a JSON value. |
TO_JSON_STRING | Converts a SQL value to a 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 NULLFLOAT64
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;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 NULLJSON_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_EXTRACT
Note: This function is deprecated. Consider usingJSON_QUERY.JSON_EXTRACT(json_string_expr,json_path)JSON_EXTRACT(json_expr,json_path)Description
Extracts a JSON value and converts it to aSQL JSON-formattedSTRING orJSON value.This function uses single quotes and brackets 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_EXTRACT("null","$")-- Returns a SQL NULLjson_expr: JSON. For example:JSON'{"class": {"students": [{"name": "Jane"}]}}'Extracts a JSON
nullwhen a JSONnullis encountered.SELECTJSON_EXTRACT(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_EXTRACT(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_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------* | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"Jane"}]}} | *-----------------------------------------------------------*/SELECTJSON_EXTRACT('{"class": {"students": []}}','$')ASjson_text_string;/*-----------------------------------------------------------* | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[]}} | *-----------------------------------------------------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------* | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} | *-----------------------------------------------------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[0]')ASfirst_student;/*-----------------* | first_student | +-----------------+ | {"name":"Jane"} | *-----------------*/SELECTJSON_EXTRACT('{"class": {"students": []}}','$.class.students[0]')ASfirst_student;/*-----------------* | first_student | +-----------------+ | NULL | *-----------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[0]')ASfirst_student;/*-----------------* | first_student | +-----------------+ | {"name":"John"} | *-----------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[1].name')ASsecond_student;/*----------------* | second_student | +----------------+ | NULL | *----------------*/SELECTJSON_EXTRACT('{"class": {"students": []}}','$.class.students[1].name')ASsecond_student;/*----------------* | second_student | +----------------+ | NULL | *----------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": null}]}}','$.class.students[1].name')ASsecond_student;/*----------------* | second_student | +----------------+ | NULL | *----------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[1].name')ASsecond_student;/*----------------* | second_student | +----------------+ | "Jamie" | *----------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}',"$.class['students']")ASstudent_names;/*------------------------------------* | student_names | +------------------------------------+ | [{"name":"Jane"}] | *------------------------------------*/SELECTJSON_EXTRACT('{"class": {"students": []}}',"$.class['students']")ASstudent_names;/*------------------------------------* | student_names | +------------------------------------+ | [] | *------------------------------------*/SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',"$.class['students']")ASstudent_names;/*------------------------------------* | student_names | +------------------------------------+ | [{"name":"John"},{"name":"Jamie"}] | *------------------------------------*/SELECTJSON_EXTRACT('{"a": null}',"$.a");-- Returns a SQL NULLSELECTJSON_EXTRACT('{"a": null}',"$.b");-- Returns a SQL NULLSELECTJSON_EXTRACT(JSON'{"a": null}',"$.a");-- Returns a JSON 'null'SELECTJSON_EXTRACT(JSON'{"a": null}',"$.b");-- Returns a SQL NULLJSON_EXTRACT_ARRAY
Note: This function is deprecated. Consider usingJSON_QUERY_ARRAY.JSON_EXTRACT_ARRAY(json_string_expr[,json_path])JSON_EXTRACT_ARRAY(json_expr[,json_path])Description
Extracts a JSON array and converts it toa SQLARRAY<JSON-formatted STRING> orARRAY<JSON> value.This function uses single quotes and brackets 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_EXTRACT_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_EXTRACT_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_EXTRACT_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_EXTRACT_ARRAY('["apples", "oranges", "grapes"]','$')ASstring_array;/*---------------------------------* | string_array | +---------------------------------+ | ["apples", "oranges", "grapes"] | *---------------------------------*/-- Strips the double quotesSELECTARRAY(SELECTJSON_EXTRACT_SCALAR(string_element,'$')FROMUNNEST(JSON_EXTRACT_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_EXTRACT_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_EXTRACT_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$[fruits]')ASstring_array;SELECTJSON_EXTRACT_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 single quotes and brackets,[' ']. For example:
SELECTJSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}',"$['a.b'].c")AShello;/*-----------* | hello | +-----------+ | ["world"] | *-----------*/The following examples explore how invalid requests and empty arrays arehandled:
- If a JSONPath is invalid, an error is thrown.
- If a JSON-formatted string is invalid, the output is NULL.
- It's okay to have empty arrays in the JSON-formatted string.
-- An error is thrown if you provide an invalid JSONPath.SELECTJSON_EXTRACT_ARRAY('["foo", "bar", "baz"]','INVALID_JSONPath')ASresult;-- If the JSONPath doesn't refer to an array, then NULL is returned.SELECTJSON_EXTRACT_ARRAY('{"a": "foo"}','$.a')ASresult;/*--------* | result | +--------+ | NULL | *--------*/-- If a key that doesn't exist is specified, then the result is NULL.SELECTJSON_EXTRACT_ARRAY('{"a": "foo"}','$.b')ASresult;/*--------* | result | +--------+ | NULL | *--------*/-- Empty arrays in JSON-formatted strings are supported.SELECTJSON_EXTRACT_ARRAY('{"a": "foo", "b": []}','$.b')ASresult;/*--------* | result | +--------+ | [] | *--------*/JSON_EXTRACT_SCALAR
Note: This function is deprecated. Consider usingJSON_VALUE.JSON_EXTRACT_SCALAR(json_string_expr[,json_path])JSON_EXTRACT_SCALAR(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 return values.
- Returns a SQL
NULLif a non-scalar value is selected. - Uses single quotes and brackets to escape invalidJSONPathcharacters in JSON keys. For example:
['a.b'].
Arguments:
json_string_expr: A JSON-formatted string. For example:'{"name": "Jane", "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,age is extracted.
SELECTJSON_EXTRACT_SCALAR(JSON'{"name": "Jakob", "age": "6" }','$.age')ASscalar_age;/*------------* | scalar_age | +------------+ | 6 | *------------*/The following example compares how results are returned for theJSON_EXTRACTandJSON_EXTRACT_SCALAR functions.
SELECTJSON_EXTRACT('{"name": "Jakob", "age": "6" }','$.name')ASjson_name,JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }','$.name')ASscalar_name,JSON_EXTRACT('{"name": "Jakob", "age": "6" }','$.age')ASjson_age,JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }','$.age')ASscalar_age;/*-----------+-------------+----------+------------* | json_name | scalar_name | json_age | scalar_age | +-----------+-------------+----------+------------+ | "Jakob" | Jakob | "6" | 6 | *-----------+-------------+----------+------------*/SELECTJSON_EXTRACT('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_extract,JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_extract_scalar;/*--------------------+---------------------* | json_extract | json_extract_scalar | +--------------------+---------------------+ | ["apple","banana"] | NULL | *--------------------+---------------------*/In cases where a JSON key uses invalid JSONPath characters, you can escape thosecharacters using single quotes and brackets,[' ']. For example:
SELECTJSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}',"$['a.b'].c")AShello;/*-------* | hello | +-------+ | world | *-------*/JSON_EXTRACT_STRING_ARRAY
Note: This function is deprecated. Consider usingJSON_VALUE_ARRAY.JSON_EXTRACT_STRING_ARRAY(json_string_expr[,json_path])JSON_EXTRACT_STRING_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 single quotes and brackets to escape invalidJSONPathcharacters in 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 the output contains aNULLarray element, an error isproduced because the final output can't be an array withNULLvalues. - If a JSONPath matches an array that contains scalar objects and a JSON
null,then the output of the function must be transformed because the final outputcan't be an array withNULLvalues.
Return type
ARRAY<STRING>
Examples
This extracts items in JSON to a string array:
SELECTJSON_EXTRACT_STRING_ARRAY(JSON'{"fruits": ["apples", "oranges", "grapes"]}','$.fruits')ASstring_array;/*---------------------------* | string_array | +---------------------------+ | [apples, oranges, grapes] | *---------------------------*/The following example compares how results are returned for theJSON_EXTRACT_ARRAY andJSON_EXTRACT_STRING_ARRAY functions.
SELECTJSON_EXTRACT_ARRAY('["apples", "oranges"]')ASjson_array,JSON_EXTRACT_STRING_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_EXTRACT_STRING_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_EXTRACT_STRING_ARRAY('[1, 2, 3]','$'))ASinteger_element)ASinteger_array;/*---------------* | integer_array | +---------------+ | [1, 2, 3] | *---------------*/These are equivalent:
SELECTJSON_EXTRACT_STRING_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$[fruits]')ASstring_array;SELECTJSON_EXTRACT_STRING_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 single quotes and brackets:[' ']. For example:
SELECTJSON_EXTRACT_STRING_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_EXTRACT_STRING_ARRAY('["foo", "bar", "baz"]','INVALID_JSONPath')ASresult;-- If the JSON formatted string is invalid, then NULL is returned.SELECTJSON_EXTRACT_STRING_ARRAY('}}','$')ASresult;/*--------* | result | +--------+ | NULL | *--------*/-- If the JSON document is NULL, then NULL is returned.SELECTJSON_EXTRACT_STRING_ARRAY(NULL,'$')ASresult;/*--------* | result | +--------+ | NULL | *--------*/-- If a JSONPath doesn't match anything, then the output is NULL.SELECTJSON_EXTRACT_STRING_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_EXTRACT_STRING_ARRAY('{"a": "foo"}','$')ASresult;/*--------* | result | +--------+ | NULL | *--------*/-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.SELECTJSON_EXTRACT_STRING_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_EXTRACT_STRING_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_EXTRACT_STRING_ARRAY('{"a": "foo", "b": []}','$.b')ASresult;/*--------* | result | +--------+ | [] | *--------*/-- The following query produces and error because the final output can't be an-- array with NULLs.SELECTJSON_EXTRACT_STRING_ARRAY('["world", 1, null]')ASresult;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. This functionlets youspecify a mode for the JSONPath.
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"}] | *------------------------------------*/In the following examples, the JSON data is extracted inlax mode.Because the keywordlax is included in theJSONPath, JSON arrays areautomatically unwrapped.
SELECTJSON_QUERY(JSON'{"class": {"students": [{"name": "Jane"}]}}','lax $.class.students.name')ASstudent_names_lax;/*-------------------* | student_names_lax | *-------------------* | ["Jane"] | *-------------------*/SELECTJSON_QUERY(JSON'[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]','lax $.class.students.name')ASstudent_names_lax;/*-------------------* | student_names_lax | *-------------------* | ["Joe","Jamie"] | *-------------------*/SELECTJSON_QUERY(JSON'{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}','lax $.class.students.name')ASstudent_names_lax;/*-------------------* | student_names_lax | *-------------------* | ["Jamie"] | *-------------------*/In the following examples, the JSON data is extracted inlax recursive mode.Because the keywordlax recursive is included in theJSONPath, JSON arraysare unwrapped until a non-array type is found.
SELECTJSON_QUERY(JSON'{"class": {"students": [{"name": "Jane"}]}}','lax recursive $.class.students.name')ASstudent_names_lax_recursive;/*-----------------------------* | student_names_lax_recursive | *-----------------------------* | ["Jane"] | *-----------------------------*/SELECTJSON_QUERY(JSON'[[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]]','lax recursive $.class.students.name')ASstudent_names_lax_recursive;/*-----------------------------* | student_names_lax_recursive | *-----------------------------* | ["Joe","Jamie"] | *-----------------------------*/SELECTJSON_QUERY(JSON'{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}','lax recursive $.class.students.name')ASstudent_names_lax_recursive;/*-----------------------------* | student_names_lax_recursive | *-----------------------------* | ["John","Jamie"] | *-----------------------------*/In the following examples, the keywordslax andlax recursive indicate thatnon-array types should be wrapped into arrays of size 1 before matching. Themodeslax andlax recursive behave identically for wrapping arrays.
SELECTJSON_QUERY(JSON'{"class": {"students": {"name": "Jane"}}}','lax $.class[0].students[0].name')ASstudent_names_lax,JSON_QUERY(JSON'{"class": {"students": {"name": "Jane"}}}','lax recursive $.class[0].students[0].name')ASstudent_names_lax_recursive;/*-------------------*-----------------------------* | student_names_lax | student_names_lax_recursive | *-------------------*-----------------------------* | ["Jane"] | ["Jane"] | *-------------------*-----------------------------*/SELECTJSON_QUERY(JSON'[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]','lax $.class[0].students[0].name')ASstudent_names_lax,JSON_QUERY(JSON'[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]','lax recursive $.class[0].students[0].name')ASstudent_names_lax_recursive;/*-------------------*-----------------------------* | student_names_lax | student_names_lax_recursive | *-------------------*-----------------------------* | ["Joe"] | ["Joe"] | *-------------------*-----------------------------*/SELECTJSON_QUERY(JSON'{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}','lax $.class[0].students[0].name')ASstudent_names_lax,JSON_QUERY(JSON'{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}','lax recursive $.class[0].students[0].name')ASstudent_names_lax_recursive;/*-------------------*-----------------------------* | student_names_lax | student_names_lax_recursive | *-------------------*-----------------------------* | ["John"] | ["John"] | *-------------------*-----------------------------*/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 forJSONnull. If the output contains aNULLarray element, an error isproduced because the final output can't be an array withNULLvalues. - If a JSONPath matches an array that contains scalar objects and a JSON
null,then the output of the function must be transformed because the final outputcan't be an array withNULLvalues.
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 | +--------+ | [] | *--------*/-- The following query produces and error because the final output can't be an-- array with NULLs.SELECTJSON_VALUE_ARRAY('["world", 1, null]')ASresult;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 aBIGNUMERIC value, 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 aBIGNUMERIC value, 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" | *------------------------------*/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 NULLTO_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
INT64NUMERICBIGNUMERICIf 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} | *------------------------------*/TO_JSON_STRING
TO_JSON_STRING(value[,pretty_print])Description
Converts a SQL value to a JSON-formattedSTRING value.
Arguments:
value: A SQL value. You can review the GoogleSQL data types thatthis function supports and their JSON encodingshere.pretty_print: Optional boolean parameter. Ifpretty_printistrue, thereturned value is formatted for easy readability.
Return type
A JSON-formattedSTRING
Examples
The following query converts aSTRUCT value to a JSON-formatted string:
SELECTTO_JSON_STRING(STRUCT(1ASid,[10,20]AScoordinates))ASjson_data/*--------------------------------* | json_data | +--------------------------------+ | {"id":1,"coordinates":[10,20]} | *--------------------------------*/The following query converts aSTRUCT value to a JSON-formatted string that iseasy to read:
SELECTTO_JSON_STRING(STRUCT(1ASid,[10,20]AScoordinates),true)ASjson_data/*--------------------* | json_data | +--------------------+ | { | | "id": 1, | | "coordinates": [ | | 10, | | 20 | | ] | | } | *--------------------*/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_JSON_STRINGTO_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 | (TO_JSON_STRING only) number or string Encoded as a number when the value is in the range of [-253, 253], which is the range of integers that can be represented losslessly as IEEE 754 double-precision floating point numbers. A value outside of this range is encoded as a string. | SQL input:9007199254740992JSON output: 9007199254740992SQL input: 9007199254740993JSON output: "9007199254740993" |
| INT64 | (TO_JSON only) 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 BIGNUMERIC | (TO_JSON_STRING only) number or string Encoded as a number when the value is in the range of [-253, 253] and has no fractional part. A value outside of this range is encoded as a string. | SQL input:-1JSON output: -1SQL input: 0JSON output: 0SQL input: 9007199254740993JSON output: "9007199254740993"SQL input: 123.56JSON output: "123.56" |
| NUMERIC BIGNUMERIC | (TO_JSON only) 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" |
| 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" |
| DATETIME | string Encoded as ISO 8601 date and time, where T separates the date and time. | SQL input:DATETIME '2017-03-06 12:34:56.789012'JSON output: "2017-03-06T12:34:56.789012" |
| TIME | string Encoded as ISO 8601 time. | SQL input:TIME '12:34:56.789012'JSON output: "12:34:56.789012" |
| 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} |
| RANGE | range Encoded as an object with a | SQL input:RANGE<DATE> '[2024-07-24, 2024-07-25)'JSON output: {"start":"2024-07-24","end":"2024-07-25"}SQL input: RANGE<DATETIME> '[2024-07-24 10:00:00, UNBOUNDED)'JSON output: {"start":"2024-07-24T10:00:00","end":null} |
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: |
[][][][][]... | Child subscript operator. If the object is a JSON array within an array, you can use as many additional brackets as you need to specify the child array index. | JSON-formatted string: JSON path: JSON result: |
Modes for JSONPath
Some JSON functions that take a JSONPath let you specify a mode that indicateshow the JSONPath matches the JSON data structure. For example, the JSONPathcould belax $.class.students. The following modes are supported:
| Mode | Description | Example JSONPath |
|---|---|---|
strict (default) | The JSONPath must structurally match the JSON data | "$.class.students" |
lax | Implicitly adapts the path to the structure of the JSON data. If the JSONPath doesn't exactly match the JSON data, then the following rules apply:
| "lax $.class.students" |
lax recursive | In addition tolax behavior, JSONPath unwraps consecutive arrays until a non-array type is encountered. | "lax recursive $.class.students" |
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-10-02 UTC.