Query nested jsonb using postgres

Question

I have a nested jsonb column and I'm trying to query a field in the jsonb that is LIKE a value or is that exact value using Postgres9.6

Table: test

Jsonb column: data

{
"subject":[
{
          "test1": "blue",
          "test2": "12",
          "test3": "green"
          },
          {
          "test1": "red",
          "test2": "1234",
          "test3": "green"
          }
]}

I tried this:

SELECT * from test where data ->'subject'->>'test2' like '%12%';

Also tried a query for containment:

SELECT '{"test":{test1: "blue"}}' :: jsonb@> '{"test":{}}'::jsonb

Lastly, tried to access the jsonb elements but get a column "1234" does not exist error.

SELECT * FROM test
WHERE EXISTS (SELECT FROM jsonb_array_elements(test.data -> 'subject') as s
    WHERE (s->'test2'= "1234"));

The first runs, but I get no data back.

Function that errors

CREATE FUNCTION search_data (search text)
  RETURNS setof test AS $$
SELECT *
FROM test t, jsonb_array_elements(t.data->'subject') e
WHERE e.value->>'test2' like '%search%';
$$ language sql stable;

Timshel · Accepted Answer · 2018-10-10 05:46:13Z

4

I expect you want something like:

SELECT *
FROM test t, jsonb_array_elements(t.data->'subject') e
WHERE e.value->>'test2' like '%12%';

Actually, I just noticed that you almost had it working with your last query, but there are a couple of minor syntax & logic errors:

you need single quotes not double quotes around the '1234' string literal (which is why you are getting the "no such column" error), and:
you need to use ->> rather than -> to extract the data->'test2', so it is extracted as varchar not as jsonb. Otherwise the value gets wrapped in double quotes later in the query evaluation when the jsonb is implicitly converted to varchar to compare it with the '1234'::varchar

The query below works for me:

SELECT * FROM test
WHERE EXISTS (SELECT FROM jsonb_array_elements(test.data->'subject') as s
    WHERE (s->>'test2' = '1234'));

See my db<>fiddle

edited Oct 10, 2018 at 5:46

answered Oct 10, 2018 at 5:33

Timshel

1,78312 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query nested jsonb using postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related