CODEWITHSUNDEEP
cockroachdb
Jordan Lewis
Jordan Lewis

Reputation: 17948

Does CockroachDB support JSON?

I’m interested in trying out CockroachDB, but typically SQL databases don’t natively support JSON. Is there a way for me to access fields of JSON objects in queries if I store them in CockroachDB?

Upvotes: 5

Views: 1700

Answers (2)

yoonghm
yoonghm

Reputation: 4645

CockroachDB supports JSON. It stores JSON data in JSONB data type - Binary JSON.

CREATE TABLE my_table1 (
  id   INT PRIMARY KEY,
  data JSONB
);

The size of a JSONB field is variable but should be kept within 1 MB to ensure satisfactory performance.

We can insert JSON string as follows:

INSERT INTO my_table1 (id, data)
VALUES 
    (1, '{"name": "Mary", "age": 16, "city": "Singapore"}'::JSONB),
    (2, '{"name": "John", "age": 17, "city": "Malaysia" }'::JSONB),
    (3, '{"name": "Pete", "age": 18, "city": "Vienna"   }'::JSONB),
    (99,'{"name": "Anna", "gender": "Female"            }'::JSONB);

SELECT * from my_table1;

  id |                       data
-----+---------------------------------------------------
   1 | {"age": 16, "city": "Singapore", "name": "Mary"}
   2 | {"age": 17, "city": "Malaysia", "name": "John"}
   3 | {"age": 18, "city": "Vienna", "name": "Pete"}
  99 | {"gender": "Female", "name": "Anna"}
(4 rows)

Return rows (and returning data as JSONB)

SELECT * FROM my_table1 WHERE data->'age' = '17'::JSONB;

  id |                      data
-----+--------------------------------------------------
   2 | {"age": 17, "city": "Malaysia", "name": "John"}
(1 row)

Return rows (and returning data as string)

SELECT * FROM my_table1 WHERE data->>'age' = '17';

  id |                      data
-----+--------------------------------------------------
   2 | {"age": 17, "city": "Malaysia", "name": "John"}
(1 row)

Return rows showing age and city from data field as JSONB

SELECT id,
       data->'age'  AS "age",
       data->'city' AS "city"
  FROM my_table1;

  id | age  |    city
-----+------+--------------
   1 |   16 | "Singapore"
   2 |   17 | "Malaysia"
   3 |   18 | "Vienna"
  99 | NULL | NULL
(4 rows)

Return rows if data field has age subfield

SELECT id,
       data->'age'  AS "age",
       data->'city' AS "city"
  FROM my_table1
 WHERE data ? 'age';

  id | Age |    City
-----+-----+--------------
   1 |  16 | "Singapore"
   2 |  17 | "Malaysia"
   3 |  18 | "Vienna"
(3 rows)

Return rows if age subfield (as string) is equal to 17

SELECT * FROM my_table1 WHERE data->>'age' = '17';

  id |                      data
-----+--------------------------------------------------
   2 | {"age": 17, "city": "Malaysia", "name": "John"}
(1 row)

Return rows if age subfield (as JSONB) is equal to 17

SELECT * FROM my_table1 WHERE data->'age' = '17'::JSONB;

  id |                      data
-----+--------------------------------------------------
   2 | {"age": 17, "city": "Malaysia", "name": "John"}
(1 row)

Select rows if name and gender subfields exist in the data field.

SELECT * FROM my_table1 WHERE data ?& ARRAY['name', 'gender'];

  id |                 data
-----+---------------------------------------
  99 | {"gender": "Female", "name": "Anna"}
(1 row)

Upvotes: 0

thinklinux
thinklinux

Reputation: 1377

UPDATE: CockroachDB supports JSON now.

Be Flexible & Consistent: JSON Comes to CockroachDB

We are excited to announce support for JSON in our 2.0 release (coming in April) and available now via our most recent 2.0 Beta release. Now you can use both structured and semi-structured data within the same database.

Upvotes: 4

Related Questions