cockroachdb jsonb select query

Question

I have a simple table in CockroachDB v2.0-beta:

CREATE TABLE account ( 
   id UUID NOT NULL DEFAULT uuid_v4()::UUID, 
   acct JSON NULL,
   CONSTRAINT "primary" PRIMARY KEY (id ASC),
   INVERTED INDEX account_acct_idx (acct),
   FAMILY "primary" (id, acct) 
)

I can run a select query to find specific property under acct->properties like this:

select acct->>'id' from account where acct @> '{"properties": {"foo": "bar"}}';

Is there a way to select subset of the Json blob such as nested property? Something like this would be helpful:

select acct->>'id', acct->>'properties:description' from account 
    where acct @> '{"properties": {"foo": "bar"}}';

Thanks in advance for any hints!

Cheers, ~g

I don't follow your question. What output are you trying to get, exactly? There's nothing preventing you from doing arbitrary lookups on the JSON blob returned from the contains query. — Jordan Lewis

Rebecca Taft Rebecca Taft · Accepted Answer · 2020-12-15T21:03:34

As Jordan mentioned in the comment above, you can already get nested JSON objects using the operators you've referenced in your question. Using your example, I can access the nested object as follows:

> CREATE TABLE account (
   id UUID NOT NULL DEFAULT uuid_v4()::UUID,
   acct JSON NULL,
   CONSTRAINT "primary" PRIMARY KEY (id ASC),
   INVERTED INDEX account_acct_idx (acct),
   FAMILY "primary" (id, acct)
);
CREATE TABLE

> INSERT INTO account (acct) VALUES ('{"properties": {"foo": "bar"}}');
INSERT 1

> SELECT * FROM account;
                   id                  |              acct
---------------------------------------+---------------------------------
  6fe08368-7720-4ddd-885e-75437b4e0267 | {"properties": {"foo": "bar"}}
(1 row)

> SELECT acct->>'properties' FROM account WHERE acct @> '{"properties": {"foo": "bar"}}';
     ?column?
------------------
  {"foo": "bar"}
(1 row)

Note that ->> returns a string representation of the nested JSON object. As described in our JSON docs, the operator -> can be used to return the object itself. This also allows you to chain the operator if you want to access a deeper nested object. For example:

> select acct->'properties'->'foo' from account;
  ?column?
------------
  "bar"
(1 row)

cockroachdb jsonb select query

1 Answers