Sign inTry Free

JSON_EXTRACT_<type>

On this page

The JSON_EXTRACT_<type> function extracts data from JSON, optionally using a path specification.

When used with a JSON object or array and a path specification, it extracts data from the JSON object or array that matches the path specification. The function returns NULL if the path does not match or if either argument is NULL.

When used with a JSON argument and no path specification, it extracts data from the JSON value as the specified <type>. This usage supports extraction from JSON string, numeric, boolean, and NULL values, in addition to JSON objects and arrays.

SingleStore supports the following JSON_EXTRACT_<type> functions:

  • JSON_EXTRACT_DOUBLE extracts numeric values and is equivalent to the ::% JSON operator.

  • JSON_EXTRACT_STRING extracts text and binary values and is equivalent to the ::$ JSON operator. The result collation is controlled by the json_extract_string_collation engine variable. Refer to Sync Variables Lists for more information.

  • JSON_EXTRACT_JSON extracts any valid JSON value, including true, false, JSON maps, and JSON lists.

  • JSON_EXTRACT_BIGINT extracts BIGINT values. For other data types booleans (true and false) are converted to 1 and 0, respectively, and floating-point values are rounded to the nearest integer (for example, 1.4 becomes 1, and 1.5 becomes 2).

The :: operator can be applied to columns of any scalar type (JSON, BLOB, INT, and so on). It does not work with ROW expression or RECORD type, such as ROW('value1', 'value2')::field_name. Note that ROW is not a type; it is an expression that returns a RECORD type. Applying the :: operator to a RECORD or ARRAY value returns an error. The :: operator also cannot be used on user-defined function (UDF) outputs. To access values from a ROW expression, define the individual named columns in the query.

Note

Refer to Accessing Fields in a JSON Object for more information on JSON operators ::, ::$, and ::%. Refer to Character Encodings and Collation for information on overriding collation settings.

Syntax

JSON_EXTRACT_<type>(<json>, <keypath> [, ]);


JSON_EXTRACT_<type>(<json>);

Arguments

  • json: valid JSON, or the name of a JSON column.

  • keypath: (optional) The path specification; a comma-separated list of object keys or zero-indexed array positions.

Return Value

When JSON_EXTRACT_<type> is used with a JSON object or array literal and a path specification, the return values are as listed below. Refer to JSON_EXTRACT_<type> with Path Specification for more details.

  • SQL NULL if value is JSON NULL (such as {"a": null}), or if keyname does not exist.

  • The extracted element if <json> is a valid JSON object or array and the path specified by the list of <keypath>s exists in <json>.

  • SQL NULL if <json> is not a valid JSON object or array or if the path specified by the list of <keypaths> does not exist in <json>.

  • SQL NULL if <json> is SQL NULL, an empty string, a JSON string, a JSON number, a JSON boolean value, or an empty JSON object or array.

  • JSON null for JSON_EXTRACT_JSON if the value is JSON null (such as {"a": null}).

  • SQL NULL for JSON_EXTRACT_STRING if the value is JSON null (such as {"a": null}).

  • 0 for JSON_EXTRACT_BIGINT and JSON_EXTRACT_DOUBLE if the value is JSON null (such as {"a": null}).

Refer to JSON_EXTRACT_<type> - JSON Argument Only the return values of JSON_EXTRACT_<type> when used with a JSON argument only.

JSON_EXTRACT_<type> with Path Specification

The following table summarizes the functionality and return types of JSON_EXTRACT_<type> when both a JSON object or array literal and a path specification are provided as arguments.

<json>

Function

Return

Comments

JSON Object

or

Array Literal

JSON_EXTRACT_JSON(<json>, <keypath> [,...])

json

or

SQL NULL

Extracts the JSON value found at the path (<keypath> [,...]). Value returned as JSON.

If the extracted object is a JSON string, quotes are not removed.

Returns SQL NULL if keypath is not found.

JSON_EXTRACT_STRING(<json>, <keypath> [,...])

string

or

SQL NULL

Extracts the JSON value found at the path (<keypath> [,...]). Value returned as a string.

If the extracted object is a JSON string, quotes are removed. 

Returns SQL NULL if keypath is not found.

JSON_EXTRACT_BIGINT(<json>, <keypath> [,...])

integer

or

0

If the extracted value is a JSON string, extracts a valid numeric prefix and rounds to an Integer. Value returned as a BIGINT.

Otherwise, returns 0.

JSON_EXTRACT_DOUBLE(<json>, <keypath> [,...])

double

or

0

If the extracted value is a JSON string, extracts a valid numeric prefix. Value returned as DOUBLE.

Otherwise, returns 0.

JSON_EXTRACT_<type> - JSON Argument Only

The following table summarizes the functionality and return values of JSON_EXTRACT_<type> over a JSON argument only.

For all functions, the return value matches the type of the function. JSON_EXTRACT_JSON returns a JSON value, JSON_EXTRACT_STRING returns a string value, and so on.

<json>

Function

Return

Notes

SQL NULL

JSON_EXTRACT_<type>(NULL)

SQL NULL

Empty string

JSON_EXTRACT_<type>('')

SQL NULL

JSON null

JSON_EXTRACT_JSON('null')

JSON null

Returns JSON null

JSON_EXTRACT_STRING('null')

JSON_EXTRACT_BIGINT('null')

JSON_EXTRACT_DOUBLE('null')

SQL NULL

Empty JSON Object

JSON_EXTRACT_JSON('{}')

{}

Returns JSON value.

JSON_EXTRACT_STRING('{}')

{}

Returns string value.

JSON_EXTRACT_BIGINT('{}')

JSON_EXTRACT_DOUBLE('{}')

0

Return value matches type of function.

JSON boolean value

JSON_EXTRACT_JSON('true')

JSON_EXTRACT_JSON('false')

true

false

Returns JSON value.

JSON_EXTRACT_STRING('true')

JSON_EXTRACT_STRING('false')

true

false

Returns string value.

JSON_EXTRACT_BIGINT('true')

JSON_EXTRACT_DOUBLE('true')

JSON_EXTRACT_BIGINT('false')

JSON_EXTRACT_DOUBLE('false')

1

0

Returns 1 for true, 0 for false.

Return value matches type of function.

JSON string

JSON_EXTRACT_JSON('"<string>"')

"<string>"

Does not remove quotes.

JSON_EXTRACT_STRING('"<string>"')

<string >

Removes quotes.

JSON_EXTRACT_BIGINT('"<string>"')

JSON_EXTRACT_DOUBLE('"<string>"')

numeric prefix

or

0

If string has a valid numeric prefix, returns the numeric prefix, rounded appropriately.

Returns 0 if the string does not have a valid numeric prefix.

JSON number

JSON_EXTRACT_JSON('<numeric>')

numeric value

Returns JSON value.

JSON_EXTRACT_STRING('<numeric>')

numeric value

Returns string value.

JSON_EXTRACT_BIGINT('<numeric>')

rounded numeric value

Returns BIGINT.

JSON_EXTRACT_DOUBLE('<numeric>')

numeric value

Returns DOUBLE.

JSON object

JSON_EXTRACT_JSON(<json object>)

JSON_EXTRACT_STRING(<json object>)

<json object>

Extracts full object.

JSON_EXTRACT_BIGINT(<json object>)

JSON_EXTRACT_DOUBLE(<json object>)

0

Return value matches type of function.

JSON array

JSON_EXTRACT_JSON(<json array>)

JSON_EXTRACT_STRING(<json array˘>)

<json array>

Extracts full array.

JSON_EXTRACT_BIGINT(<json array>)

JSON_EXTRACT_DOUBLE(<json array>)

0

Return value matches type of function.

Invalid JSON

JSON_EXTRACT_JSON(<invalid json>)

JSON_EXTRACT_STRING(<invalid json>)

SQL NULL

JSON_EXTRACT_BIGINT(<invalid json>)

JSON_EXTRACT_DOUBLE(<invalid json>)

0

JSON_EXTRACT_<type> with Path Specification Examples

Example 1 - JSON_EXTRACT_DOUBLE

Extracts the value for beta.

SELECT JSON_EXTRACT_DOUBLE('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'beta')
AS get_beta;
+----------+
| get_beta |
+----------+
|        2 |
+----------+

Example 2 - JSON_EXTRACT_DOUBLE

Extracts value for 1 from an array.

SELECT JSON_EXTRACT_DOUBLE('[3,4,5]', 1) AS get_result;
+------------+
| get_result |
+------------+
|          4 |
+------------+

Note

Since JSON uses zero-indexed array positions, extracting with the path 0 will extract the value 3 and the path 1 will extract the value 4 as in the example above.

Example 3 - JSON_EXTRACT_JSON

Extracts the value for the path gamma.

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma')
AS get_gamma;
+-----------+
| get_gamma |
+-----------+
| [3,4,5]   |
+-----------+

Example 4 - JSON_EXTRACT_JSON - Array value

Extracts the value for the path gamma.1. Since arrays use zero-based indexing, the function will extract the number in the second position of the array [3,4,5] which is 4 in this example.

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma', 1)
AS get_gamma;
+-----------+
| get_gamma |
+-----------+
| 4         |
+-----------+

Example 5 - JSON_EXTRACT_JSON - NULL value

The result in the following example is NULL as zeta has no value.

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'zeta')
AS get_zeta;
+-----------+
| get_zeta  |
+-----------+
| NULL      |
+-----------+

Example 6 - JSON_EXTRACT_BIGINT

The result in the next example is rounded up to the next whole integer. If the value had been 1.25 the result would have been 1.

SELECT JSON_EXTRACT_BIGINT('{"alpha":1, "beta":2, "gamma": [3,4,5], "delta":1.5}', 'delta')
AS get_delta;
+-----------+
| get_delta |
+-----------+
| 2         |
+-----------+

Example 7 - JSON_EXTRACT_STRING

The value for the path gamma is an array so the entire array is returned as a string value.

SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma')
AS get_gamma;
+-----------+
| get_gamma |
+-----------+
| [3,4,5]   |
+-----------+

Example 8 - JSON_EXTRACT_STRING

Returns the value for the path gamma which is a non-numeric string in this example.

SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": "A string"}', 'gamma')
AS get_gamma;
+-----------+
| get_gamma |
+-----------+
| A string  |
+-----------+

JSON_EXTRACT_<type> - JSON Argument Only Examples

Example 1 - JSON_EXTRACT_JSON

Extract from a JSON string with " " quotes. The JSON_EXTRACT_JSON function does not remove the " " quotes from the string.

SELECT JSON_EXTRACT_JSON('"a string"');
+---------------------------------------------+
| JSON_EXTRACT_JSON('"a string"')             |
+---------------------------------------------+
|                         "a string"          |
+---------------------------------------------+

Extract from a string without " " quotes.

SELECT JSON_EXTRACT_JSON('a string');
+----------------------------------------+
| JSON_EXTRACT_JSON('a string')          |
+----------------------------------------+
|                         NULL           |
+----------------------------------------+

The JSON_EXTRACT_JSON function with one argument will return the JSON input if the input is valid JSON or SQL NULL if the input is invalid JSON.

Extract from a JSON array. Will extract the entire array.

SELECT JSON_EXTRACT_JSON('["array element"]');
+----------------------------------------+
| JSON_EXTRACT_JSON('["array element"]') |
+----------------------------------------+
|                   ["array element"]    |
+----------------------------------------+

Extract from a JSON object with two key-value pairs, for which the values are arrays with a single element. The entire JSON object will be extracted.

SELECT JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}');
+----------------------------------------------+
| JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}') |
+----------------------------------------------+
|                     {"key2":[2],"key3":[3]}  |
+----------------------------------------------+

Example 2 - JSON_EXTRACT_STRING

Extract from a string. The JSON_EXTRACT_STRING function removes the " " quotes from the string.

SELECT JSON_EXTRACT_STRING('"a string"');
+----------------------------------+
| JSON_EXTRACT_STRING('"a string"')|
+----------------------------------+
|                    a string      |
+----------------------------------+

The JSON_EXTRACT_STRING function will return the JSON input as a string if the input is valid JSON or SQL NULL if the input is invalid JSON.

Extract from a JSON array with one element which is a string. Will extract the entire array.

SELECT JSON_EXTRACT_STRING('["array element"]');
+------------------------------------------+
| JSON_EXTRACT_STRING('["array element"]') |
+------------------------------------------+
|                     ["array element"]    |
+------------------------------------------+

Extract from a JSON object with two key-value pairs, for which the values are arrays with a single element. The entire JSON object will be extracted.

SELECT JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}');
+------------------------------------------------+
| JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}') |
+------------------------------------------------+
|                    {"key2":[2],"key3":[3]}     |
+------------------------------------------------+

Example 3 - JSON_EXTRACT_BIGINT

Extract from a JSON double value. Rounds to the nearest integer.

SELECT JSON_EXTRACT_BIGINT('3.14');
+-------------------------------+
| JSON_EXTRACT_BIGINT('3.14')   |
+-------------------------------+
|                          3    |
+-------------------------------+

Extract from a JSON string with a numeric prefix. Extracts a valid numeric prefix from the string and rounds to the nearest integer.

SELECT JSON_EXTRACT_BIGINT('"12.3a"');
+-------------------------------+
| JSON_EXTRACT_BIGINT('"12.3a"')|
+-------------------------------+
|                       12      |
+-------------------------------+

Example 4 - JSON_EXTRACT_DOUBLE

Extract from a JSON double value.

SELECT JSON_EXTRACT_DOUBLE('3.14');
+-------------------------------+
| JSON_EXTRACT_DOUBLE('3.14')   |
+-------------------------------+
|                          3.14 |
+-------------------------------+

Extract from a JSON string containing a double value. Extracts a valid numeric prefix from the string.

SELECT JSON_EXTRACT_DOUBLE('"12.3a"');
+-------------------------------+
| JSON_EXTRACT_DOUBLE('"12.3a"')|
+-------------------------------+
|                       12.3    |
+-------------------------------+

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In