Postgres Querying/filtering JSONB with nested arrays

Question 1

Below is my sample requirement

I want customers who meet all the below conditions

In country "xyz", incorporated between 2019 to 2021.
Should be having at least one account with balance between 10000 and 13000 and branch is "abc" and transaction dates between 20200110 and 20210625. It is formatted and stored as number
Should be having at least one address in the state "state1" and pin codes between 625001 and 625015

Below is table structure

        CREATE TABLE IF NOT EXISTS customer_search_ms.customer
        (
            customer_id integer,
            customer_details jsonb
        )

There can be millions of rows in the table. I have created GIN index of type jsonb_ops on the customer_details column as we would also be checking for existence conditions and range comparison

Below is a sample data in the customer_data JSONB column

customer_id : 1

    {
        "customer_data": {
            "name": "abc",
            "incorporated_year": 2020,
            "country":"xyz",
            "account_details": [
                {
                    "transaction_dates": [
                        20180125, 20190125, 20200125,20200525
                    ],
                    "account_id": 1016084,
                    "account_balance": 2000,
                    "account_branch": "xyz"
                },
                {
                    "transaction_dates": [
                        20180125, 20190125, 20200125
                    ],
                    "account_id": 1016087,
                    "account_balance": 12010,
                    "account_branch": "abc"
                }
            ],
            "address": [
                {
                    "address_id": 24739,
                    "door_no": 4686467,
                    "street_name":"street1",
                    "city": "city1",
                    "state": "state1",
                    "pin_code": 625001
                },
                {
                    "address_id": 24730,
                    "door_no": 4686442,
                    "street_name":"street2",
                    "city": "city1",
                    "state": "state1",
                    "pin_code": 625014
                }
            ]
        }
    }

Now the query i have written for above is

SELECT  c.customer_id,
        c.customer_details
FROM customer_search_ms.customer c
WHERE c.customer_details @@ CAST('$.customer_data.country ==  "xyz" && $.customer_data.incorporated_year >= 2019 && $.customer_data.incorporated_year <= 2021 ' AS JSONPATH)
AND c.customer_details @? CAST('$.customer_data.account_details[*] ? (@.account_balance >=  10000) ? (@.account_balance <=  13000) ?(@.account_branch ==  "abc") ? (@.transaction_dates >=  20200110) ? (@.transaction_dates <=  20210625)' AS JSONPATH)
AND c.customer_details @? CAST('$.customer_data.address[*] ? (@.state ==  "state1") ? (@.pin_code >=  625001) ? (@.pin_code <= 625015)  ' AS JSONPATH)

To handle above scenario is it the best way to write. Is it possible to combine all the 3 criteria's (customer/account/address) into one expression? The table will have millions of rows. I am of the opinion having it as one expression and hitting the DB will give the best performance. Is it possible to combine these 3 conditions as one expression

Question 2

Your query does not give me the error you report. Rather, it runs, but does give the "wrong" results compared to what you want. There are several mistakes in it which are not syntax errors, but just give wrong results.

Your first jsonpath looks fine. It is a Boolean expression, and @@ checks if that expression yields true.

Your second jsonpath has two problems. It yields a list of objects which match your conditions. But objects are not booleans, so @@ will be unhappy and return SQL NULL, which is treated the same as false here. Instead, you need to test if that list is empty. This is what @? does, so use that instead of @@. Also, your dates are stored as 8-digit integers, but you are comparing them to 8-character strings. In jsonpath, such cross-type comparisons yield JSON null, which is treated the same as false here. So you either need to change the storage to strings, or change the literals they are compared to into integers.

Your third jsonpath also has the @@ problem. And it has the reverse of the type problem, you have the pin_code stored as strings, but you are testing them against integers. Finally you have 'pin_code' misspelled in one occurrence.

jjanes · Answer 1 · 2021-11-24T20:58:29

Your query does not give me the error you report. Rather, it runs, but does give the "wrong" results compared to what you want. There are several mistakes in it which are not syntax errors, but just give wrong results.

Your first jsonpath looks fine. It is a Boolean expression, and @@ checks if that expression yields true.

Your second jsonpath has two problems. It yields a list of objects which match your conditions. But objects are not booleans, so @@ will be unhappy and return SQL NULL, which is treated the same as false here. Instead, you need to test if that list is empty. This is what @? does, so use that instead of @@. Also, your dates are stored as 8-digit integers, but you are comparing them to 8-character strings. In jsonpath, such cross-type comparisons yield JSON null, which is treated the same as false here. So you either need to change the storage to strings, or change the literals they are compared to into integers.

Your third jsonpath also has the @@ problem. And it has the reverse of the type problem, you have the pin_code stored as strings, but you are testing them against integers. Finally you have 'pin_code' misspelled in one occurrence.

Thanks Janes. I have corrected the code and data in the original post. Due to confidential nature i had to post cooked data and did mistake in that. I am not able to reproduce the error scenario. Is there any better approach for query given above with 3 conditions in the where clause. My thinking is if i am able to make it as a single condition instead of 3 it will be better. Any guidance will be of great help to me. Thanks

Postgres Querying/filtering JSONB with nested arrays

In other languages

This page is in other languages

Popular in the category