1

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';
  • What to count exactly? The number of elements in all JSON arrays incident_data -> 'field_52ca447762ba2' of qualifying rows in incidents_incident? That's what your query does now. Or the number of qualifying rows, plus the surplus if there are more than 1 elements in the array? That's what you get from @Daniel's answer. More explanation for LEFT JOIN LATERAL: dba.stackexchange.com/a/83935/3684 – Erwin Brandstetter Aug 12 '15 at 15:05
  • Aside: Typically, you do not want to include the 00:00 of the next day in your selection, so you probably want: AND dt_created < '2015-08-10 00:00:00'. – Erwin Brandstetter Aug 12 '15 at 15:10
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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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