How to query on multiple attributes in the same json object array?

I have a json array similar to this structure in a column of my database –

{
"id": "123abc",
"Y/N": "Y",
"Color": "Purple",
"arr": [ {
   "time": 1210.55
   "person": "Sean"
   "action": "yes"   //The values for this field can only be 'yes', 'no, 'maybe'
   },
   {
   "time": 1230.19
   "person": "Linda"
   "action": "no"
  } ],
}

I need to pull all the corresponding attributes based on 2 criteria of an object in the “arr” array. I want to get the latest “arr” object based on the “time” (highest value) but only pull this index if the "action" is equal to ‘no’ or ‘yes’, so exclude all the objects when "action" = "maybe".

I have tried using a WHERE statement to have a “time” range set and ORDER BY DESC to pull the latest entry and return the entire “arr”. This just returns the highest value of “time” but returns all the attributes when "action" = "maybe" but I want to return the objects with only “yes” or “no”.

Here is the current query I have –

SELECT jsonb_build_object('ID', t.col -> '_id',
                          'Yes or No', t.col -> 'Y/N',
                          'arr', x.elem)
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT elem
   FROM   jsonb_array_elements(t.col -> 'arr') a(elem)
   WHERE a.elem -> 'time' between '1110.23' and '1514.12'
   AND t.col ->> 'Color' = 'Purple'
   ORDER  BY a.elem -> 'time' DESC NULLS LAST
   LIMIT  1
   ) x;

The query is returning the latest object in the array with the highest time but it is also returning objects when "action" = "maybe". I have tried adding AND a.elem -> 'action' = 'yes' after the WHERE statement but receive an error saying the Token “yes” is invalid.

Is it possible to return an object with the largest “time” that has the “action” attribute equal to “yes” or “no” only?

Answer

Your code is alternatively using -> and ->>, but it isn’t using them correctly.

Lots of good details here: What is the difference between `->>` and `->` in Postgres SQL?.

Everywhere in the code using -> should be ->> if you try that in the where clause with AND a.elem ->> 'action' = 'yes' it should work.

Advertisement

Answer