CRUD with JSON in MemSQL

memsql-json.sql

create database acme;

use acme;

create table customer (
  id int,
  name varchar(200),
  properties json not null,
  key (id) using clustered columnstore
);

insert into customer (
  id, name, properties
) values (
  1, 'customer 1', '{"code":"cust1", "val": 1, "arr":[1,2,3,4]}'
), (
  2, 'customer 2', '{"code":"cust2", "val": 2, "arr":[11,12,13,14]}'
);

select * from customer;

-- select json properties
select id, name, properties::$code as code,
  properties::%val as val, properties::arr as arr
from customer;

-- insert into json
update customer
set properties = JSON_SET_STRING(properties, 'acct', 'foo')
where id = 1;

select id, properties from customer;

-- update json
UPDATE customer
SET properties = JSON_SET_STRING(properties, 'code', 'foo')
where id = 1;

select id, properties from customer;

-- delete json property
update customer
set properties = JSON_DELETE_KEY(properties, 'code')
where id = 1;

select id, properties from customer;

-- add to json array
update customer
set properties::arr = JSON_ARRAY_PUSH_DOUBLE(properties::arr, 5)
where id = 1;

select id, properties from customer;

-- select and format as json
select to_json(customer.*) from customer where id = 1;

select to_json(sel.*) from (
  select id, name from customer where id = 1
) sel;

-- join on json array
select id, name, table_col
from customer
join table(json_to_array(customer.properties::arr));