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;

nested_json.sql

-- Working With Nested JSON
-- https://github.com/singlestore-labs/singlestoredb-samples/blob/main/JSON/Analyzing_nested_arrays.sql

-- https://docs.singlestore.com/managed-service/en/create-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/using-json.html
-- Working with Nested Arrays in a JSON Column

-- Database Configuration

create database if not exists documentation_s2;
use documentation_s2;
DROP TABLE IF EXISTS json_tab;

-- Create table
CREATE TABLE json_tab (`id` INT(11) DEFAULT NULL,`jsondata` JSON COLLATE utf8_bin);

-- Insert values

INSERT INTO json_tab VALUES 
( 8765 ,' {"city":"SFO","sports_teams":[{"sport_name":"football","teams":  [{"club_name":"Raiders"},{"club_name":"49ers"}]},
{"sport_name":"baseball","teams" : [{"club_name":"As"},{"club_name":"SF Giants"}]}]}') ; 

INSERT INTO json_tab VALUES 
( 9876,'{"city":"NY","sports_teams" : [{ "sport_name":"football","teams" : [{ "club_name":"Jets"},{"club_name":"Giants"}]},
{"sport_name":"baseball","teams" : [ {"club_name":"Mets"},{"club_name":"Yankees"}]},
{"sport_name":"basketball","teams" : [{"club_name":"Nets"},{"club_name":"Knicks"}]}]}');

-- Query Table as is

select * from json_tab;

-- Query Table by flattening the arrays using JSON_TO_ARRAY

WITH t AS(
SELECT id, jsondata::city city , table_col AS sports_clubs FROM json_tab JOIN TABLE(JSON_TO_ARRAY(jsondata::sports_teams))),
t1 AS(
SELECT t.id, t.city, t.sports_clubs::sport_name sport, table_col AS clubs FROM t JOIN TABLE(JSON_TO_ARRAY(t.sports_clubs::teams)))
SELECT t1.id, t1.city,t1.sport,t1.clubs::club_name club_name FROM t1;

-- Query Table by flattening the arrays using JSON_TO_ARRAY with filtering on club_name

WITH t AS
(SELECT id, jsondata::city city , table_col AS sports_clubs FROM json_tab JOIN TABLE(JSON_TO_ARRAY(jsondata::sports_teams))),
t1 AS
(SELECT t.id, t.city, t.sports_clubs::sport_name sport, table_col AS clubs FROM t JOIN TABLE(JSON_TO_ARRAY(t.sports_clubs::teams))) 
SELECT t1.id, t1.city,t1.sport,t1.clubs::club_name club_name FROM t1 WHERE t1.clubs::$club_name = 'Yankees';