Postgresql – json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

join;jsonpostgresqlset-returning-functions

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the total count.

Instead the behaviour should be like if there's no array element in the particular json than it should still be counted in the total count value. Since I am using json_array_elemnts() in FROM clause and not WHERE clause.

Example:

SELECT count(*)
FROM incidents_incident
   , json_array_elements(incidents_incident.incident_data -> 'field_52ca447762ba2')
WHERE resort_id = 1
AND dt_created >= '2015-06-01 00:00:00'
AND dt_created <= '2015-08-10 00:00:00';

Best Answer

This query requests the cartesian product between incidents_incident and json_array_elements(...).

When one of the elements of a product is empty, the result is empty.

This result doesn't depend on the WHERE clause or the fact that one the product comes from json_array_elements(). An empty table would have the same effect.

Presumably you want an unconstrained LEFT JOIN instead of the cartesian product:

SELECT count(*)
FROM incidents_incident
LEFT JOIN json_array_elements(incidents_incident.incident_data -> 'field_52ca447762ba2')
  ON (true)
WHERE resort_id = 1
AND dt_created >= '2015-06-01 00:00:00'
AND dt_created <= '2015-08-10 00:00:00';

Related Solutions

PostgreSQL – JSON Field Performance with Large Documents

In PostgreSQL 9.3, performance will be fairly poor for large json documents. The whole document must be parsed in order to access a field, index an array, etc. It's stored on disk as a simple text representation, the json text.

PostgreSQL 9.4 is likely to change this, with support for jsonb storage on disk. It'll still have to read the whole json object from disk to access any part of it, but it won't have the same kinds of parsing overheads you'll see in 9.3. It'll also gain many of the indexing features of hstore.

In 9.3, I strongly suspect that hstore, which is stored in a compact binary representation on disk and supports a variety of indexing features, is much more appropriate for your needs.

Postgresql – Postgres function to merge two json objects with overlapping keys into one object

If you're using PostgreSQL 9.5 (at least), you can use the concatenation operator (||) on jsonb types (and you can convert json to jsonb if necessary first).

For example:

WITH test(data) AS (
    VALUES ('{
        "a" : {
            "0" : 2,
            "1" : 4,
            "3" : 6
        },
        "b" : {
            "2" : 8,
            "1" : 10,
            "4" : 12
        }
    }'::jsonb)
)
SELECT (data->'a') || (data->'b') FROM test

will produce:

{"0": 2, "1": 10, "2": 8, "3": 6, "4": 12}

(Please note that, in this particular example, the parentheses around (data->'a') matter.)

You can modify the example above to get a specific value, as you requested, for example:

SELECT (((data->'a') || (data->'b'))->'1')::text::integer FROM test

Best Answer

Related Solutions

PostgreSQL – JSON Field Performance with Large Documents

Postgresql – Postgres function to merge two json objects with overlapping keys into one object

Related Question