CockroachDB

Querying Overview

CockroachDB is a distributed SQL database developed by Cockroach Labs which is generally compatible with standard Postgres. In addition to the traditional Postgres queries, CockroachDB also supports array, JSONB, and JSON data types. There are also several supporting query functions and query syntax extensions.

A general reference for CockroachDB querying can be found at

https://www.cockroachlabs.com/docs/stable/sql-statements

For information on JSON functions see

https://www.cockroachlabs.com/docs/stable/jsonb.

Handling JSON Data

Any JSONB data type values in the answer set are automatically converted into JSON objects.
For example, consider the discussion at https://www.cockroachlabs.com/docs/v20.2/jsonb.html.
It creates a table

CREATE TABLE users (
    profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    last_updated TIMESTAMP DEFAULT now(),
    user_profile JSONB
  );

Then inserts data

INSERT INTO users (user_profile) VALUES
    ('{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}'),
    ('{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}');

Next it runs a query

SELECT * FROM users;

and then shows traditional pgsql output

  

Notice the user_profile column data is just a string.

Running the same query in Qarbine’s Data Source Designer shows the following

  

Select a row shows its details on the right hand side.

  

Notice the user_profile value is a real object. Conversions to Arrays happen automatically as well. This enables much easier analytics than manually trying to deal with the simple string.

This example can be found at “example/Cockroach/Users with JSON”.

To improve performance consider creating an index on any JSONB column. For further information see https://www.cockroachlabs.com/docs/v24.2/inverted-indexes.

Manipulating Row Shape

Qarbine can be directed to further modify the answer set elements This is done using ‘pragmas” which are discussed in the general Data Source Designer documentation. The most common pragma likely to be used is “pullFieldsUp CSV_list_of_fields”. For example,

#pragma pullFieldsUp user_profile
SELECT * FROM users

results in the following results.

  

Select a row shows its details on the right hand side.

  

The fields that were previously within the user_profile object have been pulled up a level. This makes it more convenient to reference field values in a template formula. For example, instead of using ‘#user_profile.firstName” you can simply use “#firstName”.

This example can be found at “example/Cockroach/Users with JSON with pragma”.

Timestamp Handling

You can mix regular CockroachDB JSON functions with Qarbine pragmas to achieve various results. Information on CockroachDB casting can be found at https://www.cockroachlabs.com/docs/v20.2/jsonb.html#supported-casting-and-conversion.

Consider a table that has a JSONB column containing a field storing a timestamp as a string. A sample query is

select * from foo

A sample row is shown below.

  

CockroachDB can cast the string value “15-sept-2024” within the answer set it produces. For example,

select *, (something->>'when')::TIMESTAMP AS when
from foo

A sample row is shown below.

  

An alternative is to use a Qarbine pragma,

#pragma convertToDate something.when
select * from foo

A sample row for this query specification which matches the previous one is shown below.

  

This example can be found at “example/Cockroach/JSON timestamp options”.

Troubleshooting

If errors occur while using Qarbine then a good course of action is to run the query using your traditional Postgres tools. This can range from psql or PGAdmin for example. There are also troubleshooting steps described at https://www.cockroachlabs.com/docs/stable/troubleshooting-overview