Semi-Structure supporting

ClapDB supports semi-structured data, which means you can write and query JSON data, if you don’t know the schema of the data. This is useful when you have a lot of data with different schema, and you don’t want to define a schema for each data.

jsonb

Like PostgreSQL, ClapDB supports jsonb data type, which is a binary format for JSON data. You can store JSON data in a column with jsonb data type, and query it with JSON operators.

DDL

if user want to use jsonb type, just use DDL in your DDL statement.

CREATE TABLE json_table (
  id  uint64,
  value jsonb
);

even you can create multiple jsonb columns in same table:

CREATE TABLE json_table (
  id  uint64,
  value1 jsonb,
  value2 jsonb
);

ingesting jsonb

We support the standard JSON format.

https://www.tbray.org/ongoing/When/201x/2017/12/14/rfc8259.html

Below are some examples of valid JSON.

null
"aasd"
12313
[1, 2.0 ,3, "4", true ,null]
{"a":[13,4,354]}

query for jsonb

id	value
1	{“a” : 1}
2	“abc”
3	“{”a”:{”b”:”c”}}”
4	{”a”:[1,2,3,{”a”:2}]}

query for jsonb

-- Filter for 'a' in JSON where value is an integer (1)
select id, value from json_table where value->'a' = '1';
id, value
1, {"a":1}

--- JSON does not contain "a"
select id, value from json_table where value->'a' is null;
id, value
2, "123"

---- Query nested JSON
select id, value from json_table where value->'a'->'b' = '\"c\"';
id, value
3, {"a":{"b":"c"}}

---- Group or order by JSON type
select first(id), value->'a' from json_table group by value->'a' order by value->'a';
id, value
2, null
1, 1
4, [1,2,3, {"a":2}]
3, {"b":"c"}

---- Query an element inside an array (negative index starts from the end of the array)
select first(id),value->'a' from json_table where value->'a'->-1 = '{\"a\":2}'
id, value
4, [1,2,3, {"a":2}]

Tuning jsonb query

jsonb is tradeoff between flexibility and performance, if you have a fixed schema, you should use the dynmaic table, which is more efficient than jsonb. check schemaless for more details.

Flexible will hurt performance

If the schema of the jsonb data you input varies significantly, it will degrade to a raw string, and the query performance will be relatively poor

Indexing

Like other types, indexing can improve query performance, but due to the flexibility of jsonb, not all situations can benefit from indexing. Users can enhance query performance more effectively through materialized views, but this comes with increased computational and storage costs.

Materialized view

TODO