I have json column inside my PostgreSQL table that looks something similar to this:

{"example--4--":"test 1","another example--6--":"test 2","final example--e35b172a-af71-4207-91be-d1dc357fe8f3--Equipment":"ticked"}

{"example--4--":"test 4","another example--6--":"test 5","final example--e35b172a-af71-4207-91be-d1dc357fe8f3--Equipment":"ticked"}

Each key contains a map which is separated by --. The prefix is unique, ie: "example", "another example" and "final example".

I need to query on the unique prefix and so far, nothing I'm trying is even close.

select some_table.json_column from some_table
left join lateral (select array(select * from json_object_keys(some_table.json_column) as keys) k on true
where (select SPLIT_PART(k::text, '--', 1) as part_name) = 'example'
and some_table.json_column->>k = 'test 1'

The above is resulting in the following error (last line):

operator does not exist: json -> record

My expected output would be any records where "example--4--":"test 1" is present (in my above example, the only result would be)

{"example--4--":"test 1","another example--6--":"test 2","final example--e35b172a-af71-4207-91be-d1dc357fe8f3--Equipment":"ticked"}

Any help appreciated. After debugging around for a while, I can see the main issue resolves in the implicit cast to ::text. k seems to be a "record" of the keys that I need to loop and split to compare, currently, I'm casting a record to text which is causing the issue.

CodePudding user response：

One way to do it, is to use an EXIST condition together with jsonb_each_text()

select *
from the_table
where exists (select *
              from jsonb_each_text(data) as x(key,value)
              where x.key like 'example%'
                and x.value = 'test 1')

If your column isn't a jsonb (which it should be), you need to use json_each_text() instead

Another option is to use a JSON path expression:

select *
from the_table
where data @? '$.keyvalue() ? (@.key like_regex  "^example" && @.value == "test 1")'