For Postgres 9.4+ see adamc’s later answer. Or: Query for array elements inside JSON type Original answer for Postgres 9.3 Yes, that’s possible: SELECT * FROM tbl t, json_array_elements(t.json_col->’emails’) AS elem WHERE elem->>’id’ = 123; tbl being your table name, json_col the name of the JSON column. See also: How do I query using fields … Read more
In Postgres 9.3 or later use a LATERAL join: SELECT v.col_a, v.col_b, f.* — no parentheses, f is a table alias FROM v_citizenversions v LEFT JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true WHERE f.col_c = _col_c; Why LEFT JOIN LATERAL … ON true? Record returned from function has columns concatenated For older versions, there is … Read more
jsonb in Postgres 9.4+ You can use the same query as below, just with jsonb_array_elements(). But rather use the jsonb “contains” operator @> in combination with a matching GIN index on the expression data->’objects’: CREATE INDEX reports_data_gin_idx ON reports USING gin ((data->’objects’) jsonb_path_ops); SELECT * FROM reports WHERE data->’objects’ @> ‘[{“src”:”foo.png”}]’; Since the key objects … Read more