Reputation: 1509
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
Upvotes: 0
Views: 62
Reputation: 5469
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%'
Upvotes: 1