lateral

Postgres analogue to CROSS APPLY in SQL Server

by

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

Query for array elements inside JSON type

by

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

tech