Pull request from Json in Postgres
I have a json object in my postgres db that looks like the one below
{"Actor":[{"personName":"Shashi Kapoor","characterName":"Prem"},{"personName":"Sharmila Tagore","characterName":"Preeti"},{"personName":"Shatrughan Sinha","characterName":"Dr. Amar"]}
Edited (from editor: left original because this is invalid json, in my edit I fixed it)
{
"Actor":[
{
"personName":"Shashi Kapoor",
"characterName":"Prem"
},
{
"personName":"Sharmila Tagore",
"characterName":"Preeti"
},
{
"personName":"Shatrughan Sinha",
"characterName":"Dr. Amar"
}
]
}
the column name is be xyz
and I have a corresponding one content_id
.
I need to get content_ids
from that Actor & personName = Sharmila Tagore
.
I've tried many requests, among those where these two are, where a very possible request, but still I didn't get it.
SELECT content_id
FROM content_table
WHERE cast_and_crew #>> '{Actor,personName}' = '"C. R. Simha"'
...
SELECT cast_and_crew ->> 'content_id' AS content_id
FROM content_table
WHERE cast_and_crew ->> 'Actor' -> 'personName' = 'C. R. Simha'
source to share
You should use jsonb_array_elements()
to search in a nested jsonb array:
select content_id, value
from content_table,
lateral jsonb_array_elements(cast_and_crew->'Actor');
content_id | value
------------+-----------------------------------------------------------------
1 | {"personName": "Shashi Kapoor", "characterName": "Prem"}
1 | {"personName": "Sharmila Tagore", "characterName": "Preeti"}
1 | {"personName": "Shatrughan Sinha", "characterName": "Dr. Amar"}
(3 rows)
The column value
is of type jsonb
, so you can use the operator on it ->>
:
select content_id, value
from content_table,
lateral jsonb_array_elements(cast_and_crew->'Actor')
where value->>'personName' = 'Sharmila Tagore';
content_id | value
------------+--------------------------------------------------------------
1 | {"personName": "Sharmila Tagore", "characterName": "Preeti"}
(1 row)
Note that if you are using json
(not jsonb
) use of json_array_elements()
course.
source to share