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.

• Colin 't Hart: Can you show us what you've tried so far?
• Jeremy Baker: @Colin'tHart Unfortunately I don't seem to have many of the queries in my psql history. Most of my attempts involved thinking of it like a straight forward query: SELECT * FROM jobs WHERE "Details" -> 'tasks' ->> 'due_on' = '2015年08月09日' style. I kept trying to figure out the where query.. I wasn't even thinking of breaking it into a join like @Andomar suggested below.

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.

• Jeremy Baker: Awesome. I wouldn't have thought to use a CROSS JOIN like that. I think my understanding of what's returned by json_array_elements was lacking.