Working With Multi-Level JSON in CockroachDB

This tutorial demonstrates the ability in CockroachDB to work with nested JSON.

Artem Ervits

CORE ·

Jul. 08, 21 · Tutorial

Likes (4)

Comment

Save

8.5K Views

Motivation

I had a customer inquiring about whether CockroachDB is capable of working with multi-level JSON. Considering their JSON would have up to 3 levels of hierarchy, they wanted to know whether CockroachDB is able to use native capability to access data multiple levels down. This prompted my interest and led to this tutorial. Surprisingly, CockroachDB does not inhibit any limitations to the number of levels in hiearchy and performance can be improved using various optimizations also discussed below.

Start a Single Node Instance With Max SQL Memory Flag and Connect to It

    Java
   
   cockroach start-single-node --max-sql-memory=.25 --insecure --background
cockroach sql --insecure --host=localhost

Create a Table

    SQL
   
 

   CREATE TABLE sample (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), 
    payload JSONB,
    INVERTED INDEX jsonb_inv_idx (payload)
);
  

Load a Sample JSON Payload

Given the following sample json object 10 levels deep

    Java
   
 

   {
  "level1": "l1",
  "n2": {
    "level2": "l2",
    "n3": {
      "level3": "l3",
      "n4": {
          "level4": "l4",
          "n5": {
              "level5": "l5",
              "n6": {
                  "level6": "l6",
                  "n7": {
                      "level7": "l7",
                      "n8": {
                          "level8": "l8",
                          "n9": {
                              "level9": "l9",
                              "n10": {
                                  "level10": "l10"
                              }
                          }
                      }
                  }
              }
          }
      }
    }
  }
}
  

turns to an insert statement in CockroachDB that looks like so

    Java
   
 

   UPSERT INTO sample (payload) VALUES ('{
  "level1": "l1",
  "n2": {
    "level2": "l2",
    "n3": {
      "level3": "l3",
      "n4": {
          "level4": "l4",
          "n5": {
              "level5": "l5",
              "n6": {
                  "level6": "l6",
                  "n7": {
                      "level7": "l7",
                      "n8": {
                          "level8": "l8",
                          "n9": {
                              "level9": "l9",
                              "n10": {
                                  "level10": "l10"
                              }
                          }
                      }
                  }
              }
          }
      }
    }
  }
}');
  

Our table definition includes an inverted index called jsonb_inv_idx, our JSON payload will benefit from an index as you will see shortly

Query the Table

Simply querying the record will yield

    Java
   
   root@localhost:26257/defaultdb> SELECT * FROM sample;
                   id                  |                                                                                                                   payload
---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  cdd0c860-9969-4fc9-a36d-a8907f876728 | {"level1": "l1", "n2": {"level2": "l2", "n3": {"level3": "l3", "n4": {"level4": "l4", "n5": {"level5": "l5", "n6": {"level6": "l6", "n7": {"level7": "l7", "n8": {"level8": "l8", "n9": {"level9": "l9", "n10": {"level10": "l10"}}}}}}}}}}
(1 row)

Time: 735µs

That's not as interesting as if we started to reach into the payload and querying the specific levels.

Let's query level 1

    Java
   
   SELECT payload ->'level1' as level FROM sample;

  level
---------
  "l1"
(1 row)

Time: 919µs

Let's query level 2

    Java
   
   SELECT payload ->'n2' as level FROM sample;

      level
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  {"level2": "l2", "n3": {"level3": "l3", "n4": {"level4": "l4", "n5": {"level5": "l5", "n6": {"level6": "l6", "n7": {"level7": "l7", "n8": {"level8": "l8", "n9": {"level9": "l9", "n10": {"level10": "l10"}}}}}}}}}
(1 row)

This will return the entire tree starting with level2. What we need is to access the value at the appropriate level. We're going to use the -> notation to do that next.

    Java
   
   SELECT payload ->'n2'->'level2' as level FROM sample;

  level
---------
  "l2"
(1 row)

Now that we learned how to access the values in multi-level JSOn, let's query level 5

    Java
   
   SELECT payload ->'n5'->'level5' as level FROM sample;

  level
---------
  NULL
(1 row)

This is not what we expected. We have to use the -> notation to specify each level.

    Java
   
   SELECT payload ->'n2'->'n3'->'n4'->'n5'->'level5' as level FROM sample;

  level
---------
  "l5"
(1 row)

Up until now our query did not use an index to fetch the results. We can observe that with the following explain plan

    Java
   
 

   root@localhost:26257/defaultdb> EXPLAIN SELECT payload ->'n2'->'n3'->'n4'->'n5'->'leve
l5' as level FROM sample;
    tree    |    field    |  description
------------+-------------+-----------------
            | distributed | true
            | vectorized  | false
  render    |             |
   └── scan |             |
            | table       | sample@primary
            | spans       | FULL SCAN
(6 rows)

Time: 694µs
  

Notice we're doing a full table scan.

Let's wrap the query in a where clause, we can use one of the available comparison operators to check whether value exists.

    Java
   
   SELECT payload as level FROM sample WHERE payload ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->'n8'->'n9'->'n10'->'level10' = '"l10"';

                        level
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  {"level1": "l1", "n2": {"level2": "l2", "n3": {"level3": "l3", "n4": {"level4": "l4", "n5": {"level5": "l5", "n6": {"level6": "l6", "n7": {"level7": "l7", "n8": {"level8": "l8", "n9": {"level9": "l9", "n10": {"level10": "l10"}}}}}}}}}}
(1 row)

Time: 1.013ms

It can also be expressed the following way

    Java
   
   SELECT payload as level FROM sample WHERE payload ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->'n8'->'n9'->'n10' @> '{"level10": "l10"}';

Prefixing the select statement with EXPLAIN will validate the use of the inverted index

    Java
   
 

   root@localhost:26257/defaultdb> EXPLAIN SELECT payload as level FROM sample WHERE payl
oad ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->'n8'->'n9'->'n10'->'level10' = '"l10"';
     tree    |    field    |                                                               description
-------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------
             | distributed | false
             | vectorized  | false
  index-join |             |
   │         | table       | sample@primary
   │         | key columns | id
   └── scan  |             |
             | table       | sample@jsonb_inv_idx
             | spans       | /"n2"/"n3"/"n4"/"n5"/"n6"/"n7"/"n8"/"n9"/"n10"/"level10"/"l10"-/"n2"/"n3"/"n4"/"n5"/"n6"/"n7"/"n8"/"n9"/"n10"/"level10"/"l10"/PrefixEnd
(8 rows)
  

Before we move on, I should mention that we can retrieve the values as strings instead of JSON using ->> operator.

    Java
   
 

   root@localhost:26257/defaultdb> SELECT payload ->'n2'->>'level2' AS level FROM sample;
  level
---------
  l2
(1 row)

Time: 732µs

root@localhost:26257/defaultdb> SELECT payload ->'n2'->'level2' AS level FROM sample;
  level
---------
  "l2"
(1 row)

Time: 665µs
  

There are other formatting options available in our docs.

Computed Columns

Admittedly, the syntax becomes a bit cumbersome to write every time. Luckily, CockroachDB has ability to create a computed column on the JSONB column. One must still write the syntax once but accessing the fields after becomes less of a hassle.

    SQL
   
 

   CREATE TABLE sample2 (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    payload JSONB,
    level1 STRING AS (payload->>'level1') STORED,
    level2 STRING AS (payload ->'n2'->>'level2') STORED,
    level3 STRING AS (payload ->'n2'->'n3'->>'level3') STORED,
    level4 STRING AS (payload ->'n2'->'n3'->'n4'->>'level4') STORED,
    level5 STRING AS (payload ->'n2'->'n3'->'n4'->'n5'->>'level5') STORED,
    level6 STRING AS (payload ->'n2'->'n3'->'n4'->'n5'->'n6'->>'level6') STORED,
    level7 STRING AS (payload ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->>'level7') STORED,
    level8 STRING AS (payload ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->'n8'->>'level8') STORED,
    level9 STRING AS (payload ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->'n8'->'n9'->>'level9') STORED,
    level10 STRING AS (payload ->'n2'->'n3'->'n4'->'n5'->'n6'->'n7'->'n8'->'n9'->'n10'->>'level10') STORED
);
  

Now a simple query can get at any one of the fields

    Java
   
   SELECT level1, level2, level3, level4, level5, level6, level7, level8, level9, level10 FROM sample2;

  level1 | level2 | level3 | level4 | level5 | level6 | level7 | level8 | level9 | level10
---------+--------+--------+--------+--------+--------+--------+--------+--------+----------
  l1     | l2     | l3     | l4     | l5     | l6     | l7     | l8     | l9     | l10
(1 row)

Performance on writes to the table with computed columns is also a lot better than using an inverted index but this is a topic for another day.

JSON Database CockroachDB Java (programming language) sql Inverted index

Opinions expressed by DZone contributors are their own.

Related

Trending