Create Computed Column from JSONB column (containing json arrays) in Cockroachdb

Question

Is there a way we can split a JSON containing array of strings into computed columns?

credit_cards column is of jsonb type with below sample data:

[{"bank": "HDFC Bank", "cvv": "8253", "expiry": "2020-05-31T14:22:34.61426Z", "name": "18a81ea99250bf236a5e27a762a32d62", "number": "c4ca4238acf96a36"}, {"bank": "HDFC Bank", "cvv": "9214", "expiry": "2020-05-30T21:44:55.173339Z", "name": "6725a156df733ec2dd33b94f06ee2e06", "number": "c81e728dacf96a36"}, {"bank": "HDFC Bank", "cvv": "1161", "expiry": "2020-05-31T07:59:28.458905Z", "name": "eb102765424d07d8b713211c14e837b4", "number": "eccbc87eacf96a36"}]

I tried this, but looks like its not supported in Computed Column.

alter table users_combined add column projected_number STRING[] AS (json_array_elements(credit_cards)->>'number') STORED;
ERROR:  json_array_elements(): generator functions are not allowed in computed column

Another alternative which worked was:

alter table users_combined add column projected_number STRING[] AS (ARRAY[credit_cards->0->>'number',credit_cards->1->>'number',credit_cards->2->>'number']) STORED;

However this has a problem of user having to specify the "indices" of credit_card array. If we've more than 3 credit cards then we'll have to alter the column with new indices.

So is there a way to create Computed Column without having to specify the indices?

Jordan Lewis Jordan Lewis · Accepted Answer · 2020-12-15T21:01:24

There is no way to do this. But, inverted indexes are a way to get the same capabilities for looking up data in the table that I think you're going for here.

If you create an inverted index on the table, then you can search for rows that have a given number attribute efficiently:

[email protected]:26257/defaultdb> create table users_combined (credit_cards jsonb);
CREATE TABLE

Time: 3ms total (execution 3ms / network 0ms)

[email protected]:26257/defaultdb> create inverted index on users_combined(credit_cards);
CREATE INDEX

Time: 53ms total (execution 4ms / network 49ms)

[email protected]:26257/defaultdb> explain select * from users_combined where credit_cards->'number' = '"c4ca4238acf96a36"';
                                          info
----------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ table: users_combined@primary
  │
  └── • scan
        table: users_combined@users_combined_credit_cards_idx
        spans: [/'{"number": "c4ca4238acf96a36"}' - /'{"number": "c4ca4238acf96a36"}']
(10 rows)

Create Computed Column from JSONB column (containing json arrays) in Cockroachdb

1 Answers