POSTGRESQL : select data with where clause condition from object in json data type-CodePudding

I have data in database from tabel data_for_filter and column src_data (type: JSON) like this

{"filter": {"name": "Anton", "activity": "Studying"},"primary": true, "position": 1}

in postgresql, how I can do search query with where clause name like '%Here%' ?

i was try this query

select src_data from data_for_filter where src_data ->> 'filter' #>> 'name' like '%Here%'

CodePudding user response：

->> returns a text, so you can't apply #>> on that. -> returns a proper jsonb (or json) value. The #>> operator however requires an array, so #>> 'name' would lead to the next error

where src_data -> 'filter' ->> 'name' like '%Here%'

you can simplify this using:

where src_data #>> '{filter,name}' like '%Here%'

CodePudding user response：

Unless I’m misunderstanding, you’re wanting to get the JSON object field by the key “filter”, so you’re using the incorrect operator. Try -> ‘filter’

Here’s a reference:

https://www.postgresql.org/docs/current/functions-json.html