Q:
postgres jsonb search for all elements with a certain value in all arrays
I have the following jsonb column
{
"url": {
"location": {
"description": [
"The historic site of Tampō Station, in Kiyama, Nara, Japan."
]
},
"url_canon": "",
"id": "65"
},
"id": "65"
}
and I want to select all documents that contain all of the following:
any "description": {
"content": "The historic site of Tampō Station, in Kiyama, Nara, Japan."
}
and all "content": "The historic site of Tampō Station, in Kiyama, Nara, Japan."
which means documents that have "description": [{...}, {...}] in all "location": {...}, etc.
I tried
select * from geocities where jb_data->'location.description' @> 'description:[a-zA-Z0-9.:_-]';
which is good for testing but is not working. I guess what I am asking is how to search for every element in every array of every object in a jsonb array.
A:
I've read a bit about the postgres JSON support in relation to other databases, but at least as far as I know, there's no good support for JSON nested objects in SQL or JPQL. So you'd have to use an application level language like Java or Javascript.
The best way to do this is to write your own function in a procedural language that maps the structure of your data
Related links: