The following query parses a text column in a table to obtain a 'PO' value. It then returns a table with two columns - 'PO' and 'DATA' where each cell essentially contains all the row data as a JSONB object:
select POed."PO", jsonb_agg(to_jsonb(POed)- 'PO') as "DATA"
from (
select * ,
(case
when patd."Text" notnull
then (regexp_match(patd."Text" , '^[0-9]{10}00'))[1]
else null
end) as "PO"
from "pat_details" patd
) as POed
group by "PO"
A simpler example is if I start with a table:
| a | b | Text |
|---|---|---|
| 1 | 2 | xxx 453 yyy |
| 4 | 5 | www 453 www |
The result looks like this where each element of DATA is a list of json objects:
| PO | DATA |
|---|---|
| 453 | [{"a": 1, "b":2},{"a": 4, "b":5}] |
I want to further filter the result to show say all "PO" with "a"=4. I thought something like:
WHERE "DATA" -> 0 -> 0."a" = "4"
That generates a syntax error. What is the error/correct way to query this?
Note 1: WHERE "DATA"->0->>'a'='4' will get me all instances where the first element of the list has the criteria but then do I have to repeat the query for all possible elements?
The answer is:
WHERE "DATA"@>'[{"a":"4"}]'
Note that if you try to specify a specific element as I did you will run in to problems. It looks like the elements in the jsonb are randomly ordered so if you (as I did) search on the first element, you will probably get a different answer each query.