Sign inTry Free

JSON_EXTRACT_<type>

On this page

The JSON_EXTRACT_<type> function extracts data from a JSON object or array using a path specification. The function returns NULL if the path does not match or if either argument is NULL.

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> [, …]);

Arguments

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

  • keypath: 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}).

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> 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  |
+-----------+

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