1

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.

2
  • Can you show us what you've tried so far? Jun 8, 2015 at 8:23
  • @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. Jun 8, 2015 at 8:28

1 Answer 1

2

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.

1
  • 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. Jun 8, 2015 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.