Built-in JSON functions and documentations

General
#1

Hello crdb friends,

I’m looking for a reference docs on built-in functions supported by crdb. I read the following pages, but could not find more detailed documentation:

From the last page, I found the function I needed json_object_keys(input: jsonb) → string for my query:

Upon query execution, I received an error message:

Query 1:
root@:26257/test_crdb> select json_object_keys(data->'perflogs') as data from "ProcessLogs" limit 1;
pq: column name "data" not found

root@:26257/test_crdb> show create table "ProcessLogs";
+---------------+--------------------------------------------------------------------------+
|     Table     |                               CreateTable                                |
+---------------+--------------------------------------------------------------------------+
| "ProcessLogs" | CREATE TABLE "ProcessLogs" (                                             |
|               |                                                                          |
|               |     key STRING(128) NOT NULL,                                            |
|               |                                                                          |
|               |     data JSON NOT NULL,                                                  |
|               |                                                                          |
|               |     iso_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,                     |
|               |                                                                          |
|               |     unix_timestamp INT NOT NULL,                                         |
|               |                                                                          |
|               |     source STRING NOT NULL,                                              |
|               |                                                                          |
|               |     CONSTRAINT "primary" PRIMARY KEY (key ASC),                          |
|               |                                                                          |
|               |     INDEX "ProcessLogs_source_unix_timestamp_idx" (source ASC,           |
|               | unix_timestamp ASC),                                                     |
|               |                                                                          |
|               |     FAMILY "primary" (key, data, iso_timestamp, unix_timestamp, source)  |
|               |                                                                          |
|               | )                                                                        |
+---------------+--------------------------------------------------------------------------+
(1 row)

And if I were to run a simpler select query, it would run just fine…

root@:26257/test_crdb> select data->'perflogs' as data from "ProcessLogs" limit 1;
+--------------------------------------------------------------------------+
|                                   data                                   |
+--------------------------------------------------------------------------+
| {"07e9e530-ea12-11e8-b170-51f9d11d2758": {"crumb":                       |
... /*result abridged*/

The same query statement executes successfully in RDS PostgreSQL

my_dev_db=> select jsonb_object_keys(data->'perflogs') as data from "ProcessLogs" limit 1;
                 data
--------------------------------------
 c35b2a0e-ede7-11e8-b5a2-c93693164324
(1 row)

my_dev_db=> \d "ProcessLogs";
                         Table "public.ProcessLogs"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 key            | character varying(128)   |           | not null |
 data           | jsonb                    |           | not null |
 iso_timestamp  | timestamp with time zone |           | not null |
 unix_timestamp | bigint                   |           | not null |
 source         | text                     |           | not null |
Indexes:
    "ProcessLogs_pkey" PRIMARY KEY, btree (key)
    "ProcessLogs_source_unix_timestamp_idx" btree (source, unix_timestamp)

I know in the crdb case I use json data type instead of jsonb (even though i specified it as jsonb in the create table DDL, but perhaps that’s a separate topic) as in the case of pg, but when I replaced it to using jsonb_object_keys function it still failed. Am I missing something in the crdb case?

Thanks in advance for the help.

#2

Hi @antonius.lin,

I just tested this scenario locally using cockroach demo, and I didn’t see any problems:

root@127.0.0.1:53171/defaultdb> create table t1 (a serial primary key, data jsonb);
CREATE TABLE

root@127.0.0.1:53171/defaultdb> show create table t1;
  table_name |               create_statement
+------------+-----------------------------------------------+
  t1         | CREATE TABLE t1 (
             |     a INT NOT NULL DEFAULT unique_rowid(),
             |     data JSONB NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (a ASC),
             |     FAMILY "primary" (a, data)
             | )
(1 row)

Time: 4.067ms

root@127.0.0.1:53171/defaultdb> insert into t1 (data) values ('{"outer key 1": {"inner key 1": "innner value 1", "inner key 2": "innner value 2"}}');
INSERT 1

Time: 3.172ms

root@127.0.0.1:53171/defaultdb> select data from t1;
                                         data
+-------------------------------------------------------------------------------------+
  {"outer key 1": {"inner key 1": "innner value 1", "inner key 2": "innner value 2"}}
(1 row)

Time: 5.179ms

root@127.0.0.1:53171/defaultdb> select json_object_keys(data) as data from t1 limit 1;
     data
+-------------+
  outer key 1
(1 row)

root@127.0.0.1:53171/defaultdb> select json_object_keys(data -> 'outer key 1') as data from t1 limit 1;
     data
+-------------+
  inner key 1
(1 row)

Time: 1.24ms

root@127.0.0.1:53171/defaultdb> select json_object_keys(data -> 'outer key 1') as data from t1;
     data
+-------------+
  inner key 1
  inner key 2
(2 rows)

Time: 1.272ms

I think it’s odd that your show create table shows the data column with the type json instead of jsonb. In my testing, the type would always be converted and shown as jsonb. Not sure if that matters here. In any case, please open a GitHub issue with full reproduction steps and one of our devs will take a look.

Thanks,
Jesse

#3

Hi @jesse,

Thanks for your response. I’ve opened a GitHub issue as you’ve suggested. I also provided a reproduction issue as much as possible (with an abridged data query result, which I hope would be okay).

By the way, upon reading your response earlier, I also had found in CockroachDB docs that JSON and JSONB in CockroachDB are equivalent and aliases to each other [source].

In any case, I’m still uncertain what exactly is the issue (the data typing or the function) or whether they’re even related.

Thanks again and Happy Thanksgiving.

#4

Hi @jesse,
I’m referencing another reply from the opened Github issue that solves the topic I raised here.

tl/dr; I had used v.2.0.6. This problem doesn’t exist in v.2.1.1. I had started using crdb on 2018/10/19, and between that time and today 20/10/22,we happened to have experienced two version bumps, to v.2.1, and another minor to v.2.1.1

Thank you again for your response and help.

#5

Thanks for being so proactive and doing that further research, @antonius.lin. I should’ve asked what version you are on. In any case, I’m glad you’re no longer seeing this problem.

Best,
Jesse

#6

It’s my pleasure and because I enjoy studying and using CRDB.