Sign inTry Free

JSON Functions

On this page

SingleStore provides functions and operators for creating, querying, searching, modifying, and formatting JSON values. Use these functions to construct JSON documents, extract values, update objects and arrays, and convert between JSON and SQL data types.

The following sections categorize JSON functions by their primary use case.

Creation Functions

Function

Description

Example

TO_JSON(value)

Converts a SQL value to JSON.

 
SELECT TO_JSON('hello');
+-------------+
| TO_JSON     |
+-------------+
| """hello""" |
+-------------+

TO_JSON(table_ref.*)

Converts an entire row into a JSON object.

 
SELECT TO_JSON(companies.*)
FROM companies;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TO_JSON(companies.*)                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "{""id"":2,""make"":""Honda"",""model"":""Accord"",""models"":[{""model"":""Civic"",""year"":2019},{""model"":""Accord"",""year"":2022}],""year"":2022}"               |
| "{""id"":2,""make"":""Honda"",""model"":""Civic"",""models"":[{""model"":""Civic"",""year"":2019},{""model"":""Accord"",""year"":2022}],""year"":2019}"                |
| "{""id"":1,""make"":""Toyota"",""model"":""Corolla"",""models"":[{""model"":""Camry"",""year"":2020},{""model"":""Corolla"",""year"":2021}],""year"":2021}"            |
| "{""id"":1,""make"":""Toyota"",""model"":""Camry"",""models"":[{""model"":""Camry"",""year"":2020},{""model"":""Corolla"",""year"":2021}],""year"":2020}"              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

JSON_BUILD_OBJECT(key, val, ...)

Creates a JSON object from key-value pairs.

 
SELECT JSON_BUILD_OBJECT('name', 'Ada', 'age', 30);
+----------------------------------------------+
| JSON_BUILD_OBJECT('name', 'Ada', 'age', 30)  |
+----------------------------------------------+
| "{""age"":30,""name"":""Ada""}"              |
+----------------------------------------------+

JSON_BUILD_ARRAY(val, ...)

Creates a JSON array from the specified values.

 
SELECT JSON_BUILD_ARRAY(1, 'two', 3);
+----------------------------------+
| JSON_BUILD_ARRAY(1, 'two', 3)    |
+----------------------------------+
| "[1,""two"",3]"                  |
+----------------------------------+

JSON_AGG(table_ref.*)

Aggregates an entire row into a JSON array of JSON objects.

 
SELECT JSON_AGG(companies.*)
FROM companies;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_AGG(companies.*)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "[{""id"":2,""make"":""Honda"",""model"":""Accord"",""models"":[{""model"":""Civic"",""year"":2019},{""model"":""Accord"",""year"":2022}],""year"":2022},{""id"":2,""make"":""Honda"",""model"":""Civic"",""models"":[{""model"":""Civic"",""year"":2019},{""model"":""Accord"",""year"":2022}],""year"":2019},{""id"":1,""make"":""Toyota"",""model"":""Corolla"",""models"":[{""model"":""Camry"",""year"":2020},{""model"":""Corolla"",""year"":2021}],""year"":2021},{""id"":1,""make"":""Toyota"",""model"":""Camry"",""models"":[{""model"":""Camry"",""year"":2020},{""model"":""Corolla"",""year"":2021}],""year"":2020}]"  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

JSON_AGG(expr)

Aggregates values into a JSON array.

 
SELECT JSON_AGG(make)FROM companies;
+-----------------------------------------------+
| JSON_AGG(make)                                |
+-----------------------------------------------+
| "[""Honda"",""Honda"",""Toyota"",""Toyota""]" |
+-----------------------------------------------+

Extraction Functions

Function

Description

Example

col::key (JSON_EXTRACT_JSON(json, keypath))

Extracts a JSON value and returns JSON.

 
SELECT models::`0`
FROM companies;
+---------------------------------------+
| models::`0`                           |
+---------------------------------------+
| "{""model"":""Civic"",""year"":2019}" |
| "{""model"":""Civic"",""year"":2019}" |
| "{""model"":""Camry"",""year"":2020}" |
| "{""model"":""Camry"",""year"":2020}" |
+---------------------------------------+

col::$key (JSON_EXTRACT_STRING(json, keypath))

Extracts a JSON value and returns an unquoted SQL string.

 
SELECT models::`0`::$model
FROM companies;
+---------------------+
| models::`0`::$model |
+---------------------+
| Civic               |
| Civic               |
| Camry               |
| Camry               |
+---------------------+

col::%key (JSON_EXTRACT_DOUBLE(json, keypath)

Extracts a JSON value and returns a SQL DOUBLE.

 
SELECT models::`0`::%year
FROM companies;
+----------------------+
| models::`0`::%year   |
+----------------------+
| 2019                 |
| 2019                 |
| 2020                 |
| 2020                 |
+----------------------+

JSON_EXTRACT_BIGINT(json, keypath)

Extracts a JSON value and returns a SQL BIGINT.

 
SELECT JSON_EXTRACT_BIGINT('{"a":42}', 'a')
AS bigInt;
+--------+
| bigInt |
+--------+
| 42     |
+--------+

JSON_KEYS(json)

Returns the top-level keys as a JSON array.

 
SELECT JSON_KEYS('{"a":1,"b":2}')
AS jsonKeys;
+-----------------+
| jsonKeys        |
+-----------------+
| "[""a"",""b""]" |
+-----------------+

JSON_GET_TYPE(json)

Returns the JSON type of the value.

 
SELECT JSON_GET_TYPE('[1,2]')
AS jsonGetType;
+--------------+
| jsonGetType  |
+--------------+
| array        |
+--------------+

JSON_INCLUDE_MASK(json, mask)

Returns only the keys specified by the mask.

 
SELECT JSON_INCLUDE_MASK('{"a":1,"b":2}', '{"a":1}')
AS jsonIncludeMask;
+------------------+
| jsonIncludeMask  |
+------------------+
| "{""a"":1}"      |
+------------------+

JSON_EXCLUDE_MASK(json, mask)

Returns all keys except those specified by the mask.

 
SELECT JSON_EXCLUDE_MASK('{"a":1,"b":2}', '{"a":1}')
AS jsonExcludeMask;
+------------------+
| jsonExcludeMask  |
+------------------+
| "{""b"":2}"      |
+------------------+

JSON_LENGTH(json)

Returns the number of elements in an array or keys in an object.

 
SELECT JSON_LENGTH('[1,2,3]')
AS jsonLength;
+------------+
| jsonLength |
+------------+
| 3          |
+------------+

Note

The JSON shorthand extraction operators (::, ::$, and ::%) and the JSON_EXTRACT_<type> functions support key paths that can reference values in both JSON objects and arrays.

Search Functions

Function

Description

Example

JSON_MATCH_ANY(expr, json_array)

Returns the matching element if any array element matches the predicate.

 
SELECT JSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() > 5, '[3,6,9]')
AS jsonMatchAny;
+--------------+
| jsonMatchAny |
+--------------+
| 1            |
+--------------+

JSON_MATCH_ANY_EXISTS(expr, filter_path)

Returns 1 if a value exists at the specified path.

 
SELECT JSON_MATCH_ANY_EXISTS('{"name":"Alice","age":30,"city":"NYC"}', 'name')
AS jsonMatchAnyExists;
+---------------------+
| jsonMatchAnyExists  |
+---------------------+
| 1                   |
+---------------------+

JSON_ARRAY_CONTAINS_JSON(json_array, val)

Returns 1 if the array contains the specified JSON value.

 
SELECT JSON_ARRAY_CONTAINS_JSON('[1,2,3]', 2)
AS jsonArrayContainsJson;
+------------------------+
| jsonArrayContainsJson  |
+------------------------+
| 1                      |
+------------------------+

JSON_ARRAY_CONTAINS_STRING(json_array, val)

Returns 1 if the array contains the specified string.

 
SELECT JSON_ARRAY_CONTAINS_STRING('["a","b"]', 'a')
AS jsonArrayContainsString;
+---------------------------+
| jsonArrayContainsString   |
+---------------------------+
| 1                         |
+---------------------------+

JSON_ARRAY_CONTAINS_DOUBLE(json_array, val)

Returns 1 if the array contains the specified numeric value.

 
SELECT JSON_ARRAY_CONTAINS_DOUBLE('[1.1,2.2]', 1.1)
AS jsonArrayContainsDouble;
+--------------------------+
| jsonArrayContainsDouble  |
+--------------------------+
| 1                        |
+--------------------------+

Modification Functions

Function

Description

Example

JSON_SET_JSON(json, keypath, val)

Sets or updates a key with a JSON value.

 
SELECT JSON_SET_JSON('{"a":1}', 'b', '"hi"')
AS jsonSetJson;
+--------------------------+
| jsonSetJson              |
+--------------------------+
| "{""a"":1,""b"":""hi""}" |
+--------------------------+

JSON_SET_STRING(json, keypath, val)

Sets or updates a key with a string value.

 
SELECT JSON_SET_STRING('{"a":1}', 'name', 'Ada')
AS jsonSetString;
+---------------------------------+
| jsonSetString                   |
+---------------------------------+
| "{""a"":1,""name"":""Ada""}"    |
+---------------------------------+

JSON_SET_DOUBLE(json, keypath, val)

Sets or updates a key with a numeric value.

 
SELECT JSON_SET_DOUBLE('{"a":1}', 'b', 3.14)
AS jsonSetDouble;
+--------------------------+
| jsonSetDouble            |
+--------------------------+
| "{""a"":1,""b"":3.14}"   |
+--------------------------+

JSON_DELETE_KEY(json, key)

Removes a key from a JSON object.

 
SELECT JSON_DELETE_KEY('{"a":1,"b":2}', 'b')
AS jsonDeleteKey;
+----------------+
| jsonDeleteKey  |
+----------------+
| "{""a"":1}"    |
+----------------+

JSON_ARRAY_PUSH_JSON(json_array, val)

Appends a value to a JSON array.

 
SELECT JSON_ARRAY_PUSH_JSON('[1,2]', '3')
AS jsonArrayPushJson;
+--------------------+
| jsonArrayPushJson  |
+--------------------+
| "[1,2,3]"          |
+--------------------+

JSON_ARRAY_PUSH_DOUBLE(arr, val)

Appends a value to a DOUBLE value.

 
SELECT JSON_ARRAY_PUSH_DOUBLE('[1,2]', '3.14')
AS jsonArrayPushDouble;
+-----------------------+
| jsonArrayPushDouble   |
+-----------------------+
| "[1,2,3.14]"          |
+-----------------------+

JSON_ARRAY_PUSH_STRING(arr, val)

Appends a value to a string value.

 
SELECT JSON_ARRAY_PUSH_STRING('[1,2]', 'x')
AS jsonArrayPushString;
+-----------------------+
| jsonArrayPushString   |
+-----------------------+
| "[1,2,""x""]"         |
+-----------------------+

JSON_SPLICE_JSON(arr, pos, del, val)

Removes and inserts array elements at a specified position.

 
SELECT JSON_SPLICE_JSON('[1,2,3]', 1, 1, '"x"')
AS jsonSpliceJson;
+-------------------+
| jsonSpliceJson    |
+-------------------+
| "[1,""x"",3]"     |
+-------------------+

JSON_SPICE_DOUBLE(arr, pos, del, val)

Removes and inserts array elements at a specified position.

 
SELECT JSON_SPLICE_DOUBLE('[1,2,3]', 1, 1, 4.5)
AS jsonSpliceDouble;
+---------------------+
| jsonSpliceDouble    |
+---------------------+
| "[1,4.5,3]"         |
+---------------------+

JSON_SPICE_STRING(arr, pos, del, val)

Removes and inserts array elements at a specified position.

 
SELECT JSON_SPLICE_STRING('[1,2,3]', 1, 1, 'x')
AS jsonSpliceString;
+---------------------+
| jsonSpliceString    |
+---------------------+
| "[1,""x"",3]"       |
+---------------------+

JSON_MERGE_PATCH(target, patch)

Merges JSON objects according to RFC 7396. Values in patch overwrite values in target.

 
SELECT JSON_MERGE_PATCH('{"a":1}', '{"a":2,"b":3}')
AS jsonMergePatch;
+----------------------+
| jsonMergePatch       |
+----------------------+
| "{""a"":2,""b"":3}"  |
+----------------------+

Formatting and Conversion Functions

Function

Description

Example

JSON_PRETTY(json)

Returns formatted JSON as a human-readable string. Returns TEXT, not JSON.

 
SELECT JSON_PRETTY('{"a":1}')
AS jsonPretty;
+----------------+
| jsonPretty     |
+----------------+
| "{""a"": 1}"   |
+----------------+

JSON_TO_ARRAY(json_array)

Converts a JSON array to a SingleStore ARRAY. Use TABLE() to flatten elements.

 
SELECT *
FROM TABLE(JSON_TO_ARRAY('[1,2,3]'));
+-----------+
| table_col |
+-----------+
| 1         |
| 2         |
| 3         |
+-----------+

Array Aggregation and Reduction

Function

Description

Example

REDUCE(init, array, lambda)

Applies a lambda function cumulatively to array elements.

 
SELECT REDUCE(0, JSON_TO_ARRAY('[1,2,3]'),
    REDUCE_ACC() + REDUCE_VALUE():>INT)
AS reducedValue;
+--------------+
| reducedValue |
+--------------+
| 6            |
+--------------+

TABLE(array)

Converts a SingleStore ARRAY into rows. Use JSON_TO_ARRAY() to flatten JSON arrays.

 
SELECT companies.make, companies.year, json_models.table_col
AS model_data
FROM companies
JOIN TABLE(JSON_TO_ARRAY(companies.models))
AS json_models;
+-------+------+----------------------------------------+
| make  | year | model_data                             |
+-------+------+----------------------------------------+
| Honda | 2022 | "{""model"":""Civic"",""year"":2019}"  |
| Honda | 2022 | "{""model"":""Accord"",""year"":2022}" |
| Honda | 2019 | "{""model"":""Civic"",""year"":2019}"  |
| Honda | 2019 | "{""model"":""Accord"",""year"":2022}" |
| Toyota| 2021 | "{""model"":""Camry"",""year"":2020}"  |
| Toyota| 2021 | "{""model"":""Corolla"",""year"":2021}"|
| Toyota| 2020 | "{""model"":""Camry"",""year"":2020}"  |
| Toyota| 2020 | "{""model"":""Corolla"",""year"":2021}"|
+-------+------+----------------------------------------+

Index JSON Columns

Index

Description

Example

Multi-Value Hash Index

Indexes values in JSON arrays or nested paths for fast equality lookups.

 
CREATE TABLE orders (
    id BIGINT,
    details JSON,
    MULTI VALUE INDEX mv_idx(details)
        INDEX_OPTIONS='{"TOKENIZER":"MATCH_ANY","PATH":["product_id","id"]}',
    SHARD KEY(id)
);


INSERT INTO orders VALUES
    (1, '{"product_id":{"id":"P100"},"name":"Widget"}');


SELECT *
FROM orders
WHERE JSON_MATCH_ANY(
    MATCH_PARAM_JSON() IN (TO_JSON('P100'), TO_JSON('P200')),
    details,
    'product_id',
    'id'
);
+----+----------------------------------------------+
| id | details                                      |
+----+----------------------------------------------+
| 1  | {"name":"Widget","product_id":{"id":"P100"}}  |
+----+----------------------------------------------+

Full-Text Search Index (v2)

Enables full-text search on JSON columns, with fuzzy matching, regular expressions, and per-key searches.

 
CREATE TABLE articles (
    id INT UNSIGNED,
    data JSON,
    FULLTEXT USING VERSION 2 ft_idx (data),
    SHARD KEY(id)
);


INSERT INTO articles VALUES
    (1, '{"title":"Getting Started","body":"SingleStore is a distributed database for real-time analytics"}');


OPTIMIZE TABLE articles FLUSH;


SELECT *
FROM articles
WHERE MATCH(TABLE articles) AGAINST ('data\:analytics');
+----+----------------------------------------------------------------------------------------------------+
| id | data                                                                                               |
+----+----------------------------------------------------------------------------------------------------+
| 1  | {"body":"SingleStore is a distributed database for real-time analytics","title":"Getting Started"}  |
+----+----------------------------------------------------------------------------------------------------+

Persistent Computed Column + Hash Index

Extracts a scalar value from a non-array JSON path into a persisted computed column and creates a hash index for fast equality lookups.

 
CREATE TABLE products (
    id BIGINT,
    meta JSON,
    product_id AS meta::$product_id PERSISTED TEXT,
    INDEX idx_pid (product_id),
    SHARD KEY(id)
);


INSERT INTO products (id, meta)
VALUES (1, '{"product_id":"SKU-42","name":"Gadget"}');


SELECT *
FROM products
WHERE product_id = 'SKU-42';
+----+-------------------------------------------+------------+
| id | meta                                      | product_id |
+----+-------------------------------------------+------------+
| 1  | {"name":"Gadget","product_id":"SKU-42"}   | SKU-42     |
+----+-------------------------------------------+------------+

Persistent Computed Column + Unique Index

Extracts a value from JSON into a persisted column and enforces uniqueness with a unique index.

 
CREATE TABLE users (
    id BIGINT,
    profile JSON,
    email AS profile::$email PERSISTED TEXT,
    SHARD KEY(email),
    UNIQUE INDEX idx_email (email)
);


INSERT INTO users (id, profile)
VALUES (1, '{"email":"alice@example.com","name":"Alice"}');


INSERT INTO users (id, profile)
VALUES (2, '{"email":"alice@example.com","name":"Duplicate"}');
ERROR 1062 ER_DUP_ENTRY: Leaf Error(node-b5799e62-fea4-476a-85d8-ff8d6d7a9a3e-leaf-ag2-b-0.svc-b5799e62-fea4-476a-9c01-ec70f5218e6c:3306):Duplicate entry 'alice@example.com' for key 'idx_email'

Persistent Computed Column + Full-Text Index

Extracts a text value from JSON into a persisted column and creates a full-text index on that field.

 
CREATE TABLE docs (
    id BIGINT,
    content JSON,
    body AS content::$body PERSISTED TEXT,
    FULLTEXT USING VERSION 2 ft_body (body),
    SHARD KEY(id)
);


INSERT INTO docs (id, content)
VALUES
    (1, '{"body":"SingleStore provides real-time analytics on operational data","title":"Overview"}');


OPTIMIZE TABLE docs FLUSH;


SELECT *
FROM docs
WHERE MATCH(TABLE docs) AGAINST ('body\:analytics');
+----+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
| id | content                                                                                    | body                                                                     |
+----+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
| 1  | {"body":"SingleStore provides real-time analytics on operational data","title":"Overview"} | SingleStore provides real-time analytics on operational data             |
+----+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------+

Persistent Computed Column + Vector Index

Extracts a vector embedding from JSON into a persisted column and creates a vector index for approximate nearest neighbor (ANN) search.

 
CREATE TABLE embeddings (
    id BIGINT,
    data JSON,
    vec AS data::$embedding PERSISTED VECTOR(3),
    VECTOR INDEX vec_idx (vec)
        INDEX_OPTIONS '{"metric_type":"EUCLIDEAN_DISTANCE"}',
    SHARD KEY(id)
);


INSERT INTO embeddings (id, data)
VALUES
    (1, '{"embedding":"[0.1, 0.2, 0.3]","label":"cat"}'),
    (2, '{"embedding":"[0.9, 0.8, 0.7]","label":"dog"}');


SELECT
    id,
    data::$label AS label,
    EUCLIDEAN_DISTANCE(vec, '[0.1, 0.2, 0.3]') AS distance
FROM embeddings
ORDER BY distance
LIMIT 2;
+----+-------+--------------------+
| id | label | distance           |
+----+-------+--------------------+
| 1  | cat   |         0          |
| 2  | dog   | 1.0770329459312742 |
+----+-------+--------------------+

Keypath Syntax Reference

Syntax

Returns

Description

col::key

JSON

Extracts a key as a JSON value.

col::$key

TEXT

Extracts a key as an unquoted string.

col::%key

DOUBLE

Extracts a key as a numeric value.

col::key1::key2

JSON

Navigates nested keys.

col::`0`

JSON

Extracts an array element by index.

col::key::`0`::$name

TEXT

Chains nested object and array access.

Related Topics

In this section

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