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:
- The job id of all jobs that have a task due on a specific date
- 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.
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.