5

I have a table users with a jsonb field called data. I have to retrieve all the users that have a value in that data column matching a given string. For example:

user1 = data: {"property_a": "a1", "property_b": "b1"}
user2 = data: {"property_a": "a2", "property_b": "b2"}

I want to retrieve any user that has a value data matching 'b2', in this case that will be 'user2'.

Any idea how to do this in an elegant way? I can retrieve all keys from data of all users and create a query manually but that will be neither fast nor elegant.

In addition, I have to retrieve the key and value matched, but first things first.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Juan Manuel Costello
  • 51
  • 1
  • 1
  • 2

2 Answers2

9

There is no easy way. Per documentation:

GIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of jsonb documents (datums)

Bold emphasis mine. There is no index over all values. (Those can have non-compatible data types!) If you do not know the name(s) of all key(s) you have to inspect all JSON values in every row.

If there are just two keys like you demonstrate (or just a few well-kown keys), it's still easy enough:

SELECT *
FROM   users
WHERE  data->>'property_a' = 'b2' OR
       data->>'property_b' = 'b2';

Can be supported with a simple expression index:

CREATE INDEX foo_idx ON users ((data->>'property_a'), (data->>'property_b'))

Or with a GIN index:

SELECT *
FROM   users
WHERE  data @> '{"property_a": "b2"}' OR
       data @> '{"property_b": "b2"}'

CREATE INDEX bar_idx ON users USING gin (data jsonb_path_ops);

If you don't know all key names, things get more complicated ...

You could use jsonb_each() or jsonb_each_text() to unnest all values into a set and then check with an ANY construct:

SELECT *
FROM   users
WHERE  jsonb '"b2"' = ANY (SELECT (jsonb_each(data)).value);

Or

...
WHERE  'b2' = ANY (SELECT (jsonb_each_text(data)).value);

db<>fiddle here

But there is no index support for the last one. You could instead extract all values into and array and create an expression index on that, and match that expression in queries with array operators ...

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
6

Try this query.

SELECT * FROM users
WHERE data::text LIKE '%b2%'

Of course it won't work if your key will contain such string too.

Sergey Onishchenko
  • 6,943
  • 4
  • 44
  • 51
  • actually, `=` will cause an error. The form that will work: `SELECT * FROM users WHERE data::text LIKE '%b2%'` great hack casting the JSON field to be just a text field. – andrewleung Mar 16 '17 at 12:17