Sign inTry Free

JSON_SET_<type>

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

On this page

There is a group of three JSON functions, which sets the value of a given key in a JSON map or array.

It requires the keypath to exist and will only create the last key and value if it does not exist. It will not create the entire keypath.

Maps are in the form:

{"item1":1, "item2":2, "item3": [3,4,5]}

And arrays are in the form:

[1, 2, 3]

  • JSON_SET_DOUBLE is for all numeric data. JavaScript has only one numeric type.

  • JSON_SET_STRING is for all text and binary types.

  • JSON_SET_JSON is for all valid JavaScript types, including true, false, JSON maps, and lists.

JSON_SET_<type>(json, keypath, value)

Arguments

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

  • keypath: The path to the key to set the value to (comma-separated list of dictionary keys or zero-indexed array positions).

  • value: the value to set.

If the keyname does not exist in a map, it will be added. If it does exist, the value will be overwritten. If an array index is past the end of the array, intervening values will be set to NULL.

Return Value

  • The original JSON document, modified to set the specified key path to the specified value.

  • SQL NULL if JSON is not a valid JSON map or array.

json_compatibility_level

This function allow you to set values within a JSON document which return a new, logically modified document, while leaving the original document unchanged. The json_compatibility_level engine variable controls the behavior of the  json_set_<type> functions and specifies whether the function will create missing keys if they does not exist. The default value for json_compatibility_level is 7.8, which means that missing keypaths are not created.

This behavior of creating missing keypaths is available by setting the json_compatibility_level to 8.0.

Examples

The following examples illustrate the output when  json_compatibility_level is set to 7.8.

SELECT JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 'delta', 3);
+---------------------------------------------------------------+
| JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 'delta', 3) |
+---------------------------------------------------------------+
| {"alpha":1,"beta":2}                                          |
+---------------------------------------------------------------+
SELECT JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 26);
+------------------------------------------------------+
| JSON_SET_DOUBLE('{"alpha":1, "beta":2}', 'zeta', 26) |
+------------------------------------------------------+
| {"alpha":1,"beta":2,"zeta":26}                       |
+------------------------------------------------------+
SELECT JSON_SET_JSON('{"alpha":{"beta":{"gamma": 1}}}', 'alpha', 'beta', '[1,2,3]');
+------------------------------------------------------------------------------+
| JSON_SET_JSON('{"alpha":{"beta":{"gamma": 1}}}', 'alpha', 'beta', '[1,2,3]') |  |
+------------------------------------------------------------------------------+
| {"alpha":{"beta":[1,2,3]}}                                                   |
+------------------------------------------------------------------------------+
SELECT JSON_SET_JSON('{"item1":null,"item2":[847,849]}', 'item1', 0, 'true') as item_numbers;
+----------------------------------------+
| item_numbers                           |
+----------------------------------------+
| {"item1":null,"item2":[847,849]}.      |
+----------------------------------------+
SELECT JSON_SET_JSON('[2,{"item2":false}]', 0, 'item2', null) as item_numbers;
+----------------------------+
| item_numbers               |
+----------------------------+
| [2,{"item2":false}]        |
+----------------------------+
SELECT JSON_SET_JSON('{"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79}', 'item1', '0', 'x', 'true') as item_numbers;
+------------------------------------------------------------------+
| item_numbers                                                     |
+------------------------------------------------------------------+
| {"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79} |
+------------------------------------------------------------------+

The following examples illustrate the output when  json_compatability_level is set to 8.0.

SELECT JSON_SET_JSON('{"item1":null,"item2":[847,849]}', 'item1', 0, 'true') as item_numbers;
+----------------------------------------+
| item_numbers                           |
+----------------------------------------+
| {"item1":{"0":true},"item2":[847,849]} |
+----------------------------------------+
SELECT JSON_SET_JSON('[2,{"item2":false}]', 0, 'item2', null) as item_numbers;
+-----------------------+
| item_numbers          |
+-----------------------+
| [{},{“item2”:false}]  |
+-----------------------+  
SELECT JSON_SET_JSON('{"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79}', 'item1', '0', 'x', 'true') as item_numbers;
+---------------------------------------------------------------------------------------------+
| item_numbers{“item1”:{“0":{“x”:true},“912":913,“item2”:0},“item3":[112,212,318],“item4”:79} |
+---------------------------------------------------------------------------------------------+

Note

A JSON or Javascript null value is distinct from SQL NULL.

Related Topics

See the Engine Variables Overview for information about engine variables.

Last modified: November 7, 2023

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