How can I execute a LIKE request for a jsonb key?

Question

I have the following jsonb structure:

{'this': '1', 'this_that': '0', 'this_and_that': '5'} 

How to select rows containing a LIKE statement?

 SELECT * FROM myjson WHERE j ? 'this_%' 

Returns 0 rows ... hoped this would match 'this_that' and 'this_and_that'. (Note: the characters following the "_" can vary greatly, and therefore I cannot make an exact match.)

Answer 1

Your example should not work because listing is not implied between jsonb and text types. You can force casting:

 SELECT '{"this": 1, "this_that": 0, "this_and_that": 5}'::jsonb::text like '%"this%'; 

This is not a clean solution. Some are better at unpacking json and filtering on unpacked data with a side connection

 postgres=# SELECT key FROM myjson, lateral jsonb_each_text(j) WHERE key LIKE 'this\_%'; ┌───────────────┐ │ key │ ╞═══════════════╡ │ this_that │ │ this_and_that │ └───────────────┘ (2 rows)