How can I perform a LIKE query for a jsonb key?

Question

I have the following jsonb structure:

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

How do I select rows that contain a LIKE operator?

SELECT * FROM myjson WHERE j ? 'this_%'

Returns 0 rows...was hoping it would match 'this_that' and 'this_and_that'. (Note: the characters following '_' will potentially vary greatly and therefore I am not able to do an exact match).

Responsive

what's the exact version of PostgreSQL you're using ? run select version() – Staurolite

PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.9.1-16ubuntu6) 4.9.1, 64-bit – Responsive

Answer 1

Your example should not work because there are not implicit cast between jsonb and text types. You can enforce casting:

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

It is not clean solution. Some better is unpacking json, and filtering over unpacked data with lateral join

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

Mraz