Postgresql search in json type column

Question

In postgresql db I have a 'data' column which type is json. It looks like following:

{"columns":[{"name":"cusno","displayName":"Customer number","type":"text"},{"name":"cname","displayName":"Name","type":"text"},{"name":"cadd1","displayName":"Address","type":"text"},{"name":"cstte","displayName":"State","type":"text"}],"variables":[{"name":"var1", "type":"text"}, {"name": "var2","type":"text"}]}

I need to do search for example when user types 'cu' it should return rows which columns name or display name contains this word or variables name contains this word. How should i write the where clause ?

Answer 1

Test table & data:

create table jsontest
(
jsoncolumn json
);
insert into jsontest values ('{"columns":
[{"name":"cusno","displayName":"Customer number","type":"text"},
{"name":"cname","displayName":"Name","type":"text"},
{"name":"cadd1","displayName":"Address","type":"text"},
{"name":"cstte","displayName":"State","type":"text"}],
"variables":[{"name":"var1", "type":"text"}, 
{"name": "var2","type":"text"}]}');

Query:

with data as 
( select json_array_elements( jsoncolumn ->'columns' ) as element 
 from jsontest
)
select * 
from data 
where cast(element->>'name' as varchar) like '%cu%' 
or cast(element->>'displayName' as varchar) like '%cu%';

Test:

postgres=# with data as ( select json_array_elements( jsoncolumn ->'columns' ) as element from jsontest )
select * from data where cast(element->>'name' as varchar) like '%cu%' or cast(element->>'displayName' as varchar) like '%cu%';
 element
----------------------------------------------------------------
 {"name":"cusno","displayName":"Customer number","type":"text"}
(1 row)
postgres=#

• Evan Carroll: You don't have to cast ->> returns text. You also can clean that up by using CROSS JOIN LATERAL rather than the CTE. SELECT foo.* FROM foo CROSS JOIN LATERAL jsonb_array_elements(json->'columns') AS col WHERE col->>'name' LIKE '%cu%' OR col->>'displayName' LIKE '%cu%';