sqljsonpostgresqljsonblateral

Query an array element in an JSONB Object


I have a jsonb column called data in a table called reports. Here is what report.id = 1 looks like

[
    {
        "Product": [
            {
                "productIDs": [
                    "ABC1",
                    "ABC2"
                ],
                "groupID": "Food123"
            },
            {
                "productIDs": [
                    "EFG1"
                ],
                "groupID": "Electronic123"
            }
        ],
        "Package": [
            {
                "groupID": "Electronic123"
            }
        ],
        "type": "Produce"
    },
    {
        "Product": [
            {
                "productIDs": [
                    "ABC1",
                    "ABC2"
                ],
                "groupID": "Clothes123"
            }
        ],
        "Package": [
            {
                "groupID": "Food123"
            }
        ],
        "type": "Wearables"
    }

]

and here is what report.id = 2 looks like:

[
    {
        "Product": [
            {
                "productIDs": [
                    "XYZ1",
                    "XYZ2"
                ],
                "groupID": "Food123"
            }
        ],
        "Package": [],
        "type": "Wearable"
    },
    {
        "Product": [
            {
                "productIDs": [
                    "ABC1",
                    "ABC2"
                ],
                "groupID": "Clothes123"
            }
        ],
        "Package": [
            {
                "groupID": "Food123"
            }
        ],
        "type": "Wearables"
    }
]

I am trying to get a list of all entries in reports table where at least one of data column's element has following: type = Produce AND where any elements of Product array OR any elements of Product array's groupID start with Food

So from the example above this query will only return the first index since

  1. The type = Produce
  2. groupID starts with Food for first element of Product array

The second index will be filtered out because type is not Produce.

I am not sure how to query to do AND query for groupID. Here is what I have tried to get all entries for type Produce

 select * from reports r, jsonb_to_recordset(r.data) as items(type text) where items.type like 'Produce';

Solution

  • Sample structure and result: dbfiddle

    select r.*
    from reports r
             cross join jsonb_array_elements(r.data) l1
             cross join jsonb_array_elements(l1.value -> 'Product') l2
    where l1 ->> 'type' = 'Produce'
    and l2.value ->> 'groupID' ~ '^Food';