CODEWITHSUNDEEP
sqljsonpostgresqlgreatest-n-per-group
uzla
uzla

Reputation: 525

Select with filters on nested JSON array

Postgres 10: I have a table and a query below:

CREATE TABLE individuals (
    uid character varying(10) PRIMARY KEY,
    data jsonb
);

SELECT data->'files'  FROM individuals WHERE uid = 'PDR7073706'

It returns this structure:

[
{"date":"2017-12-19T22-35-49","type":"indiv","name":"PDR7073706_indiv_2017-12-19T22-35-49.jpeg"},
{"date":"2017-12-19T22-35-49","type":"address","name":"PDR7073706_address_2017-12-19T22-35-49.pdf"}
]

I'm struggling with adding two filters by date and time. Like (illegal pseudo-code!):

WHERE 'type' = "indiv"

or like:

WHERE 'type' = "indiv" AND max('date')

It is probably easy, but I can't crack this nut, and need your help!

Upvotes: 2

Views: 2593

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

Assuming data type jsonb for lack of info.
Use the containment operator @> for the first clause (WHERE 'type' = "indiv"):

SELECT data->'files'
FROM   individuals
WHERE  uid = 'PDR7073706'
AND    data -> 'files' @> '[{"type":"indiv"}]';

Can be supported with various kinds of indexes. See:

The second clause (AND max('date')) is more tricky. Assuming you mean:
Get rows where the JSON array element with "type":"indiv" also has the latest "date".

SELECT i.*
FROM   individuals i
JOIN   LATERAL (
   SELECT *
   FROM   jsonb_array_elements(data->'files')
   ORDER  BY to_timestamp(value ->> 'date', 'YYYY-MM-DD"T"HH24-MI-SS') DESC NULLS LAST
   LIMIT  1
   ) sub ON sub.value -> 'type' = '"indiv"'::jsonb
WHERE  uid = 'PDR7073706'
AND    data -> 'files' @> '[{"type":"indiv"}]' -- optional; may help performance

to_timestamp(value ->> 'date', 'YYYY-MM-DD"T"HH24-MI-SS') is my educated guess on your undeclared timestamp format. Details in the manual here.

The last filter is redundant and optional. but it may help performance (a lot) if it is selective (only few rows qualify) and you have a matching index as advised:

AND    data -> 'files' @> '[{"type":"indiv"}]'

Related:

Upvotes: 2

Related Questions