How to access all elements of an array? in JSONB column

General
1

Below is the table created in Cassandra 3.0 using YCQL(yugabyte):

> DESCRIBE TABLE data;

CREATE TABLE store.data (
    id int PRIMARY KEY,
    details jsonb
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};

Below is the value of details column in first row:

[
    {
        "key1": "domain_1",
        "key2": "range_1",
        "key3": "somevalue"
    },
    {
        "key1": "domain_2",
        "key2": "range_4",
        "key3": "somevalue"
    },
    {
        "key1": "domain_1",
        "key2": "range_2",
        "key3": "somevalue"
    },
    {
        "key1": "domain_2",
        "key2": "range_5",
        "key3": "somevalue"
    },
    {
        "key1": "doman_1",
        "key2": "range_3",
        "key3": "somevalue"
    },
    {
        "key1": "domain_2",
        "key2": "range_6",
        "key3": "somevalue"
    },
    {
        "key1": "doman_1",
        "key2": "range_7",
        "key3": "somevalue"
    },
    {
        "key1": "domain_1",
        "key2": "range_8",
        "key3": "somevalue"
    },
    {
        "key1": "domain_1",
        "key2": "range_2",
        "key3": "somevalue"
    }
]

Below is the value of details column in second row:

[
    {
        "key1": "domain_7",
        "key2": "range_13",
        "key3": "somevalue"
    },
    {
        "key1": "domain_2",
        "key2": "range_12",
        "key3": "somevalue"
    },
    {
        "key1": "domain_7",
        "key2": "range_14",
        "key3": "somevalue"
    },
    {
        "key1": "domain_7",
        "key2": "range_15",
        "key3": "somevalue"
    },
    {
        "key1": "doman_2",
        "key2": "range_10",
        "key3": "somevalue"
    },
    {
        "key1": "domain_2",
        "key2": "range_9",
        "key3": "somevalue"
    },
    {
        "key1": "doman_7",
        "key2": "range_8",
        "key3": "somevalue"
    },
    {
        "key1": "domain_2",
        "key2": "range_15",
        "key3": "somevalue"
    },
    {
        "key1": "domain_7",
        "key2": "range_20",
        "key3": "somevalue"
    }
]

So, value in details is array of objects.

Using ycql query,
From first row in table, access any specific(say second element) of the array with query select details->1->'key_1' as key_1 from data WHERE id=1

how to access all the elements of the array(at one go) to retrieve key_1 value?

2

Hi @sham_yuga

This isn’t currently possible in YCQL, for the top level being an array. See what’s possible on our operators page for YCQL jsonb.

You can do something like below, which will return the first element of the subdetail array inside details column:

SELECT details->'subdetail'->>1 FROM table;

If you need it as a top column, you can use a LIST collection though.

While if you need it as 1 full jsonb with those operators it’s only available in YSQL.

Does any of the above work for you ?

1 Like