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

Question

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';

Answer 1

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';