Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit

CREATE TABLE test (
  data JSONB
);

INSERT INTO test(data) VALUES(
'{  "haystack": [{"search": "findthis"}, {"search": "someothervalue"}] }'
);
1 rows affected

SELECT data
      FROM test

data

{"haystack": [{"search": "findthis"}, {"search": "someothervalue"}]}

SELECT 1
      FROM test
      WHERE data->'haystack' @> '[{"search":"findthis"}]';

?column?

1

SELECT 1
      FROM test
      WHERE data->'haystack' @> '[{"search":"dontfindthis"}]';
SELECT 1
      FROM test
      WHERE data->'haystack' @> to_jsonb('[{"search":"' || 'findthis' || '"}]');