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';
incident_data -> 'field_52ca447762ba2'
of qualifying rows inincidents_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 forLEFT JOIN LATERAL
: dba.stackexchange.com/a/83935/3684 – Erwin Brandstetter Aug 12 '15 at 15:05AND dt_created < '2015-08-10 00:00:00'
. – Erwin Brandstetter Aug 12 '15 at 15:10