2

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!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
uzla
  • 515
  • 1
  • 4
  • 20

1 Answers1

2

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • That a lot for you suggestion. You are right, I should have posted table schema, I'm updated the question. First query works great, but the second (complex) one troughs an error: ERROR: column "elem" does not exist LINE 8: ) sub ON elem -> 'type' = '"indiv"'::jsonb ^ SQL state: 42703 Character: 223 Would you mind helping finding what's wrong with it? – uzla Dec 20 '17 at 14:02
  • @uzla: Oops, I changed the column name when posting. The default result column name of `jsonb_array_elements()` is `value`. Using that now. – Erwin Brandstetter Dec 20 '17 at 14:25
  • Thanks a lot! IT works! And thanks for corrections! I learn something new everyday:-) – uzla Dec 20 '17 at 14:40