Can you convert SQL to JSON?
JSON functionsBigQuery supports the following functions, which can retrieve and transform JSON data. Show Function overviewStandard JSON extraction functions (recommended)The following functions use double quotes to escape invalid JSONPath characters: "a.b". This behavior is consistent with the ANSI standard.
Legacy JSON extraction functionsThe following functions use single quotes and brackets to escape invalid JSONPath characters: ['a.b']. While these functions are supported by BigQuery, we recommend using the functions in the previous table.
Other JSON functions
JSON_EXTRACTJSON_EXTRACT(json_string_expr, json_path)Description Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.
If you want to include non-scalar values such as arrays in the extraction, then use JSON_EXTRACT. If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_EXTRACT_SCALAR. Return type A JSON-formatted STRING Examples In the following examples, JSON data is extracted and returned as JSON-formatted strings. SELECT JSON_EXTRACT(json_text, '$') AS json_text_string FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +-----------------------------------------------------------+ | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"Jane"}]}} | | {"class":{"students":[]}} | | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} | +-----------------------------------------------------------+ SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +-----------------+ | first_student | +-----------------+ | {"name":"Jane"} | | NULL | | {"name":"John"} | +-----------------+ SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +-------------------+ | second_student | +-------------------+ | NULL | | NULL | | NULL | | "Jamie" | +-------------------+ SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +------------------------------------+ | student_names | +------------------------------------+ | [{"name":"Jane"}] | | [] | | [{"name":"John"},{"name":"Jamie"}] | +------------------------------------+JSON_QUERYJSON_QUERY(json_string_expr, json_path)Description Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
If you want to include non-scalar values such as arrays in the extraction, then use JSON_QUERY. If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_VALUE. Return type A JSON-formatted STRING Examples In the following examples, JSON data is extracted and returned as JSON-formatted strings. SELECT JSON_QUERY(json_text, '$') AS json_text_string FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +-----------------------------------------------------------+ | json_text_string | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"Jane"}]}} | | {"class":{"students":[]}} | | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} | +-----------------------------------------------------------+ SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +-----------------+ | first_student | +-----------------+ | {"name":"Jane"} | | NULL | | {"name":"John"} | +-----------------+ SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +-------------------+ | second_student | +-------------------+ | NULL | | NULL | | NULL | | "Jamie" | +-------------------+ SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names FROM UNNEST([ '{"class" : {"students" : [{"name" : "Jane"}]}}', '{"class" : {"students" : []}}', '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}' ]) AS json_text; +------------------------------------+ | student_names | +------------------------------------+ | [{"name":"Jane"}] | | [] | | [{"name":"John"},{"name":"Jamie"}] | +------------------------------------+JSON_EXTRACT_SCALARJSON_EXTRACT_SCALAR(json_string_expr[, json_path])Description Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.
If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_EXTRACT_SCALAR. If you want to include non-scalar values such as arrays in the extraction, then use JSON_EXTRACT. Return type STRING Examples The following example compares how results are returned for the JSON_EXTRACT and JSON_EXTRACT_SCALAR functions. In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets, [' ']. For example: SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello; +-------+ | hello | +-------+ | world | +-------+JSON_VALUEJSON_VALUE(json_string_expr[, json_path])Description Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_VALUE. If you want to include non-scalar values such as arrays in the extraction, then use JSON_QUERY. Return type STRING Examples The following example compares how results are returned for the JSON_QUERY and JSON_VALUE functions. SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name, JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name, JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age, JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age; +-----------+-------------+----------+------------+ | json_name | scalar_name | json_age | scalar_age | +-----------+-------------+----------+------------+ | "Jakob" | Jakob | "6" | 6 | +-----------+-------------+----------+------------+ SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query, JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value; +--------------------+------------+ | json_query | json_value | +--------------------+------------+ | ["apple","banana"] | NULL | +--------------------+------------+In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes. For example: SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello; +-------+ | hello | +-------+ | world | +-------+JSON_EXTRACT_ARRAYJSON_EXTRACT_ARRAY(json_string_expr[, json_path])Description Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.
Return type ARRAY Examples This extracts the items in a JSON-formatted string to a string array: SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array; +--------------+ | string_array | +--------------+ | [1, 2, 3] | +--------------+This extracts a string array and converts it to an integer array: SELECT ARRAY( SELECT CAST(integer_element AS INT64) FROM UNNEST( JSON_EXTRACT_ARRAY('[1,2,3]','$') ) AS integer_element ) AS integer_array; +---------------+ | integer_array | +---------------+ | [1, 2, 3] | +---------------+This extracts string values in a JSON-formatted string to an array: -- Doesn't strip the double quotes SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array; +---------------------------------+ | string_array | +---------------------------------+ | ["apples", "oranges", "grapes"] | +---------------------------------+ -- Strips the double quotes SELECT ARRAY( SELECT JSON_EXTRACT_SCALAR(string_element, '$') FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element ) AS string_array; +---------------------------+ | string_array | +---------------------------+ | [apples, oranges, grapes] | +---------------------------+This extracts only the items in the fruit property to an array: SELECT JSON_EXTRACT_ARRAY( '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}', '$.fruit' ) AS string_array; +-------------------------------------------------------+ | string_array | +-------------------------------------------------------+ | [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] | +-------------------------------------------------------+These are equivalent: SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array; SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_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 those characters using single quotes and brackets, [' ']. For example: SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello; +-----------+ | hello | +-----------+ | ["world"] | +-----------+The following examples explore how invalid requests and empty arrays are handled:
JSON_QUERY_ARRAYJSON_QUERY_ARRAY(json_string_expr[, json_path])Description Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
Return type ARRAY Examples This extracts the items in a JSON-formatted string to a string array: This extracts a string array and converts it to an integer array: SELECT ARRAY( SELECT CAST(integer_element AS INT64) FROM UNNEST( JSON_QUERY_ARRAY('[1,2,3]','$') ) AS integer_element ) AS integer_array; +---------------+ | integer_array | +---------------+ | [1, 2, 3] | +---------------+This extracts string values in a JSON-formatted string to an array: -- Doesn't strip the double quotes SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array; +---------------------------------+ | string_array | +---------------------------------+ | ["apples", "oranges", "grapes"] | +---------------------------------+ -- Strips the double quotes SELECT ARRAY( SELECT JSON_VALUE(string_element, '$') FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element ) AS string_array; +---------------------------+ | string_array | +---------------------------+ | [apples, oranges, grapes] | +---------------------------+This extracts only the items in the fruit property to an array: SELECT JSON_QUERY_ARRAY( '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}', '$.fruit' ) AS string_array; +-------------------------------------------------------+ | string_array | +-------------------------------------------------------+ | [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] | +-------------------------------------------------------+These are equivalent: SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array; SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_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 those characters using double quotes: " ". For example: SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello; +-----------+ | hello | +-----------+ | ["world"] | +-----------+The following examples show how invalid requests and empty arrays are handled: -- An error is returned if you provide an invalid JSONPath. SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result; -- If the JSONPath does not refer to an array, then NULL is returned. SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a key that does not exist is specified, then the result is NULL. SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- Empty arrays in JSON-formatted strings are supported. SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result; +--------+ | result | +--------+ | [] | +--------+JSON_EXTRACT_STRING_ARRAYJSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])Description Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
Return type ARRAY Examples The following example compares how results are returned for the JSON_EXTRACT_ARRAY and JSON_EXTRACT_STRING_ARRAY functions. SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array, JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_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 quotes SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array; +-----------------+ | string_array | +-----------------+ | [foo, bar, baz] | +-----------------+This extracts a string array and converts it to an integer array: SELECT ARRAY( SELECT CAST(integer_element AS INT64) FROM UNNEST( JSON_EXTRACT_STRING_ARRAY('[1,2,3]','$') ) AS integer_element ) AS integer_array; +---------------+ | integer_array | +---------------+ | [1, 2, 3] | +---------------+These are equivalent: SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array; SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_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 those characters using single quotes and brackets: [' ']. For example: SELECT JSON_EXTRACT_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello; +---------+ | hello | +---------+ | [world] | +---------+The following examples explore how invalid requests and empty arrays are handled: -- An error is thrown if you provide an invalid JSONPath. SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result; -- If the JSON formatted string is invalid, then NULL is returned. SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If the JSON document is NULL, then NULL is returned. SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath does not match anything, then the output is NULL. SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an object that is not an array, then the output is NULL. SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an array of non-scalar objects, then the output is NULL. SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL. SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL. SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result; +--------+ | result | +--------+ | [] | +--------+ -- If a JSONPath matches an array that contains scalar values and a JSON null, -- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed -- because the final output cannot be an array with NULL values. This example -- uses the UNNEST operator to convert the output array into a table as the final output. SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value; +--------------+ | string_value | +--------------+ | world | | 1 | | NULL | +--------------+JSON_VALUE_ARRAYJSON_VALUE_ARRAY(json_string_expr[, json_path])Description Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.
Return type ARRAY Examples The following example compares how results are returned for the JSON_QUERY_ARRAY and JSON_VALUE_ARRAY functions. SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array, JSON_VALUE_ARRAY('["apples","oranges"]') AS string_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 quotes SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array; +-----------------+ | string_array | +-----------------+ | [foo, bar, baz] | +-----------------+This extracts a string array and converts it to an integer array: SELECT ARRAY( SELECT CAST(integer_element AS INT64) FROM UNNEST( JSON_VALUE_ARRAY('[1,2,3]','$') ) AS integer_element ) AS integer_array; +---------------+ | integer_array | +---------------+ | [1, 2, 3] | +---------------+These are equivalent: SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array; SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_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 those characters using double quotes: " ". For example: SELECT JSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello; +---------+ | hello | +---------+ | [world] | +---------+The following examples explore how invalid requests and empty arrays are handled: -- An error is thrown if you provide an invalid JSONPath. SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result; -- If the JSON-formatted string is invalid, then NULL is returned. SELECT JSON_VALUE_ARRAY('}}','$') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If the JSON document is NULL, then NULL is returned. SELECT JSON_VALUE_ARRAY(NULL,'$') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath does not match anything, then the output is NULL. SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an object that is not an array, then the output is NULL. SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an array of non-scalar objects, then the output is NULL. SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an array of mixed scalar and non-scalar objects, -- then the output is NULL. SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result; +--------+ | result | +--------+ | NULL | +--------+ -- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL. SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result; +--------+ | result | +--------+ | [] | +--------+ -- If a JSONPath matches an array that contains scalar objects and a JSON null, -- then the output of the JSON_VALUE_ARRAY function must be transformed -- because the final output cannot be an array with NULL values. This example -- uses the UNNEST operator to convert the output array into a table as the final output. SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value; +--------------+ | string_value | +--------------+ | world | | 1 | | NULL | +--------------+TO_JSON_STRINGTO_JSON_STRING(value[, pretty_print])Description Takes a SQL value and returns a JSON-formatted string representation of the value. The value must be a supported BigQuery data type. You can review the BigQuery data types that this function supports and their JSON encodings here. This function supports an optional boolean parameter called pretty_print. If pretty_print is true, the returned value is formatted for easy readability. Return type A JSON-formatted STRING Examples Convert rows in a table to JSON-formatted strings. With CoordinatesTable AS ( (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL (SELECT 3 AS id, [50,60] AS coordinates)) SELECT id, coordinates, TO_JSON_STRING(t) AS json_data FROM CoordinatesTable AS t; +----+-------------+--------------------------------+ | id | coordinates | json_data | +----+-------------+--------------------------------+ | 1 | [10, 20] | {"id":1,"coordinates":[10,20]} | | 2 | [30, 40] | {"id":2,"coordinates":[30,40]} | | 3 | [50, 60] | {"id":3,"coordinates":[50,60]} | +----+-------------+--------------------------------+Convert rows in a table to JSON-formatted strings that are easy to read. With CoordinatesTable AS ( (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL (SELECT 2 AS id, [30,40] AS coordinates)) SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data FROM CoordinatesTable AS t; +----+-------------+--------------------+ | id | coordinates | json_data | +----+-------------+--------------------+ | 1 | [10, 20] | { | | | | "id": 1, | | | | "coordinates": [ | | | | 10, | | | | 20 | | | | ] | | | | } | +----+-------------+--------------------+ | 2 | [30, 40] | { | | | | "id": 2, | | | | "coordinates": [ | | | | 30, | | | | 40 | | | | ] | | | | } | +----+-------------+--------------------+JSON encodingsThe following table includes common encodings that are used when a SQL value is encoded as JSON value with the TO_JSON_STRING function.
JSONPathMost JSON functions pass in a json_string_expr and json_path parameter. The json_string_expr parameter passes in a JSON-formatted string, and the json_path parameter identifies the value or values you want to obtain from the JSON-formatted string. The json_string_expr parameter must be a JSON string that is formatted like this: '{"class" : {"students" : [{"name" : "Jane"}]}}'You construct the json_path parameter using the JSONPath format. As part of this format, this parameter must start with a $ symbol, which refers to the outermost level of the JSON-formatted string. You can identify child values using dots. If the JSON object is an array, you can use brackets to specify the array index. If the keys contain $, dots, or brackets, refer to each JSON function for how to escape them.
A JSON functions returns NULL if the json_path parameter does not match a value in json_string_expr. If the selected value for a scalar function is not scalar, such as an object or an array, the function returns NULL. If the JSONPath is invalid, the function raises an error. |