PostgreSQL – Using Value of JSON Object in JSON Array

jsonpostgresqlpostgresql-9.4

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.

Best Answer

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.

Related Solutions

PostgreSQL – Create Smaller Row from Alias and Preserve Column Names

One idea is to create an inline view containing the columns you are interested in:

SELECT users."name" AS "name"
     , array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
    ON users.id=user_sites.user
CROSS JOIN LATERAL (
    SELECT name, created 
    FROM remodel.sites AS s
    WHERE s.id=user_sites.site
) AS sites
GROUP BY "users".id

By using LATERAL you can reference the "sibling" in the inline view. Since the JOIN condition is part of the view a CROSS JOIN can be used.

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Best Answer

Related Solutions

PostgreSQL – Create Smaller Row from Alias and Preserve Column Names

Postgresql – find all JSON scalars in PostgreSQL 9.3

Related Question