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;

Answer 1

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:

1. you need single quotes not double quotes around the '1234' string literal (which is why you are getting the "no such column" error), and:
2. 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