Check for value in json array

Question

I have a table of data which has a field called details which contains a json object.

The object looks something like this:

{
    "name": "Persons Name",
    "list": [
        {
            "name": "Persons Name",
            "assigned": {
                "company": "Company 1",
                "number": "AA1"
            }
        },
        {
            "name": "Persons Name",
            "assigned": {
                "company": "Company 2",
                "number": "BB2"
            }
        },
        {
            "name": "Persons Name",
            "assigned": {
                "company": "Company 3",
                "number": "AA3"
            }
        }
    ],
    "total_results": 3
}

Essentially, I want to return all data if any of the person's 'assigned'->>'number' field begins with an A. In the above example, two of the individuals numbers are prefixed with an A so I want all data returned.

I've been playing around and had have been making some progress but can't figure out how to bring it all together.

select f->'assigned'->>'number' from jsonb_array_elements((select details->'list' from table_name)) f;

The above query can get me a list of the three 'number' fields but I'm not sure how I can combine that with a query to return all the information, if any of these fields contain a prefix A

Answer 1

You are so close. Just add your condition in where clause.

SELECT * 
FROM   JSON_ARRAY_ELEMENTS((SELECT details -> 'list' FROM TABLE_NAME)) f 
WHERE  f -> 'assigned' ->> 'number' LIKE 'A%'

CHECK DEMO HERE