3

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 ?

4

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=#
  • 3
    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%'; – Evan Carroll Jun 19 '17 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.