Using the value of a JSON object that is stored in a PostgreSQL JSON array

Question

I have a table that looks like this (simplified):

Jobs
-------------------------------
ID | Details
-------------------------------
1  | { 'tasks': [] }
2  | { 'tasks': [{'id': '1', 'due_on': '2015-06-8'}, {'id': '2', 'due_on': '2015-06-09'}] }
3  | { 'tasks': [{'id': '3','due_on': '2015-06-8'}] }

I am trying to query for two different things, both based on the due_on date in the subtasks:

1. The job id of all jobs that have a task due on a specific date
2. The tasks that are due on a specific date along with their job id

With a normalized structure these would obviously be easy queries. In this case, the data is structured as a JSON object for a very specific reason that's out of this question's scope. Let's just go with it.

I have been experimenting with the JSON functions and the array functions but I just can't seem to get the query right.

For example, SELECT * FROM Jobs WHERE Details->'tasks'->>'due_on' = '2015-06-08' just throws a cannot extract field from a non-object error (for pretty clear reasons).

Alternatively, I could store the data as a tasks object instead of an array. If the above would be much easier / faster / better then I'll refactor.

Any help would be much appreciated. Thank you.

Answer 1

You can use json_array_elements to turn a JSON array into a rowset:

SELECT  "ID" as JobId
,       t->>'id' as TaskId
FROM    Table1 j
CROSS JOIN
        json_array_elements("Details"->'tasks') t
WHERE   cast(t->>'due_on' as date) = '2015-06-08';

The cast(... as date) works around the 2015-06-8 date which misses a leading 0 before the day. If your format is always YYYYMMDD, you can omit the cast.