Working With JSON in SingleStore

json_with_singlestore.sql

-- Delete a key from JSON Object
-- INPUT: {"somekey":"somevalue", "meta": {"ui": false, "miscTest": {"unit": "kWh", "value": 10}}}
UPDATE myTable
SET meta=JSON_DELETE_KEY(meta, 'miscTest')
WHERE id="85c8a446-fe24-11ed-8abf-af2f19c7e27f";
-- RESULT: {"somekey":"somevalue", "meta": {"ui": false}}


-- Update a key in JSON Object
-- INPUT: {"somekey":"somevalue", "meta": {"ui": false}}
UPDATE myTable
SET meta::$instructions='You are a helpful assistant' 
WHERE id="85c8a446-fe24-11ed-8abf-af2f19c7e27f";
-- RESULT: {"somekey":"somevalue", "meta": {"ui": false}, "instructions": "You are a helpful assistant"}


-- Update a key in JSON Object, the value from another key of that JSON Object
-- INPUT: {"somekey":"somevalue", "meta": {"ui": false, "reading_time": {"unit": "mins", "value": 10}}}
UPDATE myTable
SET meta::miscTest=JSON_SET_JSON(meta::reading_time, 'reading_time', 'miscTest')
WHERE id="85c8a446-fe24-11ed-8abf-af2f19c7e27f";
-- RESULT: {"somekey":"somevalue", "meta": {"ui": false, "reading_time": {"unit": "mins", "value": 10}, "miscTest": {"unit": "mins", "value": 10}}}


-- Find all keys of JSON Object
-- INPUT: {"somekey":"somevalue", "meta": {"ui": false, "reading_time": {"unit": "mins", "value": 10}}}
SELECT JSON_KEYS(meta) FROM myTable;
-- RESULT: ["ui", "reading_time"]


-- Updating boolean inside a JSON object
-- INPUT: {"somekey":"somevalue", "meta": {"ui": false, "reading_time": {"unit": "mins", "value": 10}}}
UPDATE myTable
SET meta=JSON_SET_JSON(meta, "ui", "true") 
WHERE id="d15";
-- RESULT: {"somekey":"somevalue", "meta": {"ui": true, "reading_time": {"unit": "mins", "value": 10}}}


-- Find all rows where the JSON Object contains a particular key
-- INPUT: {"somekey":"somevalue", "meta": {"ui": false, "miscTest": {"unit": "mins", "value": 10}}}
SELECT * FROM myTable 
WHERE JSON_ARRAY_CONTAINS_STRING(JSON_KEYS(meta), "miscTest");
-- RESULT: {"somekey":"somevalue", "meta": {"ui": false, "miscTest": {"unit": "mins", "value": 10}}}

-- Find all rows where the JSON Object DOES NOT contain a particular key
SELECT * FROM myTable WHERE NOT JSON_ARRAY_CONTAINS_STRING(JSON_KEYS(meta), "miscTest");

-- Find all rows where a particular JSON Object is empty
SELECT * FROM myTable WHERE JSON_LENGTH(meta)<1;