Postgres 16

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

-- JSON array in jsonb column
CREATE TABLE tbl (id int, js jsonb);

INSERT INTO tbl vaLUES
  (1, '[{"title":"title1"},{"title":"title2"}]')
, (2, '[{"title":"title1"},{"title":"title3"}]')
;

-- index for superior queries
CREATE INDEX tbl_js_gin_idx ON tbl USING gin (js jsonb_path_ops);

CREATE TABLE

INSERT 0 2

CREATE INDEX

-- naive query (no index support)
SELECT *
FROM   tbl, jsonb_array_elements(js) AS job
WHERE  job->>'title' = 'title2';

id
js
value

1
[{"title": "title1"}, {"title": "title2"}]
{"title": "title2"}

SELECT 1

-- better (with possible index support):
SELECT *
FROM   tbl
WHERE  js @> '[{"title":"title2"}]';

id
js

1
[{"title": "title1"}, {"title": "title2"}]

SELECT 1

-- or with jsonpath operator
SELECT *
FROM   tbl
WHERE  js @? '$[*] ? (@.title == "title2")';

id
js

1
[{"title": "title1"}, {"title": "title2"}]

SELECT 1