I have table with columns :
id, word, decode
where decode is a json array like this:
[{
"character": "三",
"components": [
"一",
"二"
]
},
{
"character": "大",
"components": [
"大"
]
},
{
"character": "洋",
"components": [
"氵",
"羊"
]
}]
I need search components in components. For example '二'
you can use json_array_elements_text(json) function to expands a json array to a set of text values, and then filter by your desired value :
select q.arrays
from
(
select json_array_elements_text(decode) as arrays
from tab
) q
where q.arrays like '%二%';
arrays
---------------------------------------------------
{ "character": "三", "components": [ "一", "二" ]}
if you exactly want to extract the sub-component which contains your character("二"),then use :
select *
from
(
select elms -> 'components' as elm
from tab t,
json_array_elements(decode) as elms
) q
where elm::text like '%二%';
elm
---------------------------------------------------
[ "一", "二" ]