Filtering Out Null Keys in JSON Output Using Apache AGE Cypher Query with json_object_agg

Question

Is there a way to filter out null keys for my json? This is using Apache AGE extension:

SELECT json_object_agg(k, v)
FROM cypher('hermech', $$
  MATCH (a:contact_name)
  WHERE id(a) = 10133099161583644
  OPTIONAL MATCH (a)-[:DIRECT_EMAIL_ADDRESS]->(b:direct_email_address)
  OPTIONAL MATCH (a)<-[:CONTACT_NAME]-(c:account_name)
  OPTIONAL MATCH (c)-[:MAIN_PHONE_NUMBER]->(d:main_phone_number)
  OPTIONAL MATCH (c)-[:MAIN_STREET_ADDRESS]->(e:main_street_address)
  OPTIONAL MATCH (c)-[:MAIN_DOMAIN]->(f:main_domain)
  WITH [a,b,c,d,e,f] AS nodes
  UNWIND nodes AS n
  RETURN label(n) AS k, n.value AS v
$$) AS (k text, v text);

It breaks because label(n) doesn't exist since f doesn't exist in the dataset so it's a non-existent key in the json output.

I tried putting a WHERE clause after UNWIND but apparently that isn't proper syntax according to the parser.

Zegarek · Accepted Answer · 2025-10-02 20:24:58Z

0

Is there a way to filter out null keys for my json

Filtering Out Null Keys in JSON Output

PostgreSQL aggregate functions work with a filter clause. Not only that, some aggregates simply skip/ignore nulls (like sum() does), but json_object_agg() doesn't happen to be one of them.
_{demo at db<>fiddle}

select json_object_agg(k,v)
from(values('a',1),('b',2),(null,3),('d',null))_(k,v);

ERROR:  field name must not be null

select json_object_agg(k,v)filter(where k is not null)
from(values('a',1),('b',2),(null,3),('d',null))_(k,v);

json_object_agg
{ "a" : 1, "b" : 2, "d" : null }

select json_object_agg(k,v)filter(where k is not null and v is not null)
from(values('a',1),('b',2),(null,3),('d',null))_(k,v);

json_object_agg
{ "a" : 1, "b" : 2 }

answered Oct 2 at 20:24

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wgm Oct 3 at 21:50

This is still good stuff, but my problem is that I can't get past label(n) without it throwing an error because one of the n's doesn't exist. The solution you are proposing happens AFTER the RETURN. Maybe this question is more of an Apache AGE question?

Collectives™ on Stack Overflow

Filtering Out Null Keys in JSON Output Using Apache AGE Cypher Query with json_object_agg

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related