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

Arun Palanisamy · Accepted Answer · 2020-02-11 13:24:53Z

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

answered Feb 11, 2020 at 13:24

Arun Palanisamy

5,4898 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check for value in json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related