This article introduces the syntax and usage of the JSONB data type in ByteHouse.

The JSONB data type is used to store data in binary format. It supports efficient querying and indexing, offering higher processing performance compared to text format JSON. It is suitable for storing semi-structured data, such as user behavior logs, configuration information, and so on.

Create table #

CREATE TABLE db.t_jsonb 
( 
    id INT, 
    data JSONB 
) 
ENGINE = CnchMergeTree 
ORDER BY tuple();

Query #

Use functions to extract values of specific types #

ByteHouse supports extracting values of JSONB, String, Int64, Float64, Bool, and other types. If the type matches, the corresponding value is returned; otherwise, the default value for that type is returned.

• Extract JSONB type
  If the type matches, the corresponding value is returned; otherwise, the default value for that type is returned.

  SELECT 
      jsonb_extract('{"a":1, "b":2}'::JSONB, '$.a') AS a, 
  FROM t_jsonb;
  

• Extract String type
  If the type matches, the corresponding value is returned; otherwise, the default value for that type is an empty string.

  SELECT 
      jsonb_extract_string('{"a":"test", "b":2}'::JSONB, '$.a') AS a,b 
  FROM t_jsonb;
  

• Extract Int64 type
  If the type matches, the corresponding value is returned; otherwise, the default value for that type is 0.

  SELECT 
      jsonb_extract_int64('{"a":1, "b":2}'::JSONB, '$.a') 
  FROM t_jsonb;
  

• Extract Float64 type
  If the type matches, the corresponding value is returned; otherwise, the default value for that type is 0.0.

  SELECT 
      jsonb_extract_float64('{"a":1.0, "b":2}'::JSONB, '$.a') 
  FROM t_jsonb;
  

• Extract Bool type
  If the type matches, the corresponding value is returned; otherwise, the default value for that type is false.

  SELECT 
      jsonb_extract_bool('{"a":true, "b":2}'::JSONB, '$.a') 
  FROM t_jsonb;
  

JSONB merge #

ByteHouse supports merging two JSONB objects and returns the merged JSONB. If there are duplicate keys in both objects, the right object overwrites the left object.

SELECT 
    jsonb_merge_patch('{"a":true, "b":2}'::JSONB, '{"c":3}'::JSONB) 
FROM t_jsonb;