Simplify this query and remove redundancy

[wgmayer0]

SELECT json_build_object(
  a_label::text, a::text,
  b_label::text, b::text,
  c_label::text, c::text,
  d_label::text, d::text,
  e_label::text, e::text,
  f_label::text, f::text
)
FROM cypher('hermech', $$
  MATCH (a:contact_name)
  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)
  WHERE id(a) = ${id}
  RETURN
    head(labels(a)) AS a_label,
    a.value AS a,
    head(labels(b)) AS b_label,
    b.value AS b,
    head(labels(c)) AS c_label,
    c.value AS c,
    head(labels(d)) AS d_label,
    d.value AS d,
    head(labels(e)) AS e_label,
    e.value AS e,
    head(labels(f)) AS f_label,
    f.value AS f
$$) AS (
  a_label agtype, 
  a agtype, 
  b_label agtype, 
  b agtype,
  c_label agtype,
  c agtype,
  d_label agtype,
  d agtype,
  e_label agtype,
  e agtype,
  f_label agtype,
  f agtype

Is there a way to get rid of all of the redundancy? Is it possible to simplify this query? Also am I able to remove null keys from the final json object returned?

[jrgemignani]

@wgmayer0 Could you elaborate as to what your end goal is? Why is the data so spread out?

Also, have you tried asking Gemini or Copilot to simplify the query? They are actually fairly decent helping out with simplifications and explanations.

[wgmayer0]

Ive asked chatgpt haha. It is hallucinating solutions. This is usually my last resort. Is gemini/copilot better to use for this? And yeah, each state of point is essentially its own node because for example, person can change companies and then the data will all have to move to the new company, so it doesn't make sense to list it all in the same node. The core logic Is in the graph query, but I don't want to have to be this explicit just to get a json object of the data

…

On Tue, Sep 30, 2025 at 1:54 PM John Gemignani ***@***.***> wrote: @wgmayer0 <https://github.com/wgmayer0> Could you elaborate as to what your end goal is? Why is the data so spread out? Also, have you tried asking Gemini or Copilot to simplify the query? They are actually fairly decent helping out with simplifications and explanations. — Reply to this email directly, view it on GitHub <#2226 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ARTB5TOGFSGKK5NE3XZIH6D3VK7WNAVCNFSM6AAAAACH5EN6SKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTINJVGU3DENY> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[jrgemignani]

@wgmayer0 I use Copilot, but generally speaking, if you can constrain the AI, you can get decent results. As for your query, I will leave you with this snippet, which should help you out -

psql-17.6-5432-pgsql=# select * from cypher('graph', $$ match (b:Book) return pg_catalog.json_build_object(label(b)::text, b.title::pg_text) $$) as (result json);
      result
------------------
 {"Book" : "Qrs"}
 {"Book" : "Qrs"}
 {"Book" : "Qrs"}
 {"Book" : "Xyz"}
 {"Book" : "Abc"}
 {"Book" : "Abc"}
 {"Book" : "Abc"}
(7 rows)

[wgmayer0]

Hmm. I can't get this to run: ``` SELECT * FROM cypher('hermech', $$ MATCH (b:contact_name) RETURN pg_catalog.json_build_object(label(b)::text, b.value::pg_text) $$) as (result json); ```

…

On Tue, Sep 30, 2025 at 2:35 PM John Gemignani ***@***.***> wrote: @wgmayer0 <https://github.com/wgmayer0> I use Copilot, but generally speaking, if you can constrain the AI, you can get decent results. As for your query, I will leave you with this snippet, which should help you out - psql-17.6-5432-pgsql=# select * from cypher('graph', $$ match (b:Book) return pg_catalog.json_build_object(label(b)::text, b.title::pg_text) $$) as (result json); result ------------------ {"Book" : "Qrs"} {"Book" : "Qrs"} {"Book" : "Qrs"} {"Book" : "Xyz"} {"Book" : "Abc"} {"Book" : "Abc"} {"Book" : "Abc"} (7 rows) — Reply to this email directly, view it on GitHub <#2226 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ARTB5TONSTU5JBS2SRT3ZIT3VLEP7AVCNFSM6AAAAACH5EN6SKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTINJVGU4TKNI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[wgmayer0]

Does your code run? It's saying b.title is invalid

…

On Tue, Sep 30, 2025 at 2:35 PM John Gemignani ***@***.***> wrote: @wgmayer0 <https://github.com/wgmayer0> I use Copilot, but generally speaking, if you can constrain the AI, you can get decent results. As for your query, I will leave you with this snippet, which should help you out - psql-17.6-5432-pgsql=# select * from cypher('graph', $$ match (b:Book) return pg_catalog.json_build_object(label(b)::text, b.title::pg_text) $$) as (result json); result ------------------ {"Book" : "Qrs"} {"Book" : "Qrs"} {"Book" : "Qrs"} {"Book" : "Xyz"} {"Book" : "Abc"} {"Book" : "Abc"} {"Book" : "Abc"} (7 rows) — Reply to this email directly, view it on GitHub <#2226 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ARTB5TONSTU5JBS2SRT3ZIT3VLEP7AVCNFSM6AAAAACH5EN6SKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTINJVGU4TKNI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[jrgemignani]

@wgmayer0 The command ran, as you can see the output :)

You will need to provide your output, if it isn't working.

[wgmayer0]

True lol. ``` postgres=# SELECT * FROM cypher('hermech', $$ MATCH (b:contact_name) RETURN pg_catalog.json_build_object(label(b)::text, b.value::pg_text) $$) as (result json); ERROR: invalid indirection syntax LINE 4: ...TURN pg_catalog.json_build_object(label(b)::text, b.value::p... ^ ```

…

On Tue, Sep 30, 2025 at 4:24 PM John Gemignani ***@***.***> wrote: @wgmayer0 <https://github.com/wgmayer0> The command ran, as you can see the output :) You will need to provide your output, if it isn't working. — Reply to this email directly, view it on GitHub <#2226 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ARTB5TOA6BGJ4JPNEL7NIJT3VLRHJAVCNFSM6AAAAACH5EN6SKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTINJVGY2TMOA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[jrgemignani]

Not sure what to tell you -

psql-17.6-5432-pgsql=# load 'age'; set search_path = ag_catalog;
LOAD
SET
psql-17.6-5432-pgsql=# select * from cypher('graph', $$ match (b:Book) return pg_catalog.json_build_object(label(b)::text, b.title::text) $$) as (result json);
      result
------------------
 {"Book" : "Qrs"}
 {"Book" : "Qrs"}
 {"Book" : "Qrs"}
 {"Book" : "Xyz"}
 {"Book" : "Abc"}
 {"Book" : "Abc"}
 {"Book" : "Abc"}
(7 rows)

psql-17.6-5432-pgsql=# select * from cypher('graph', $$ match (b:Book) return pg_catalog.json_build_object(label(b)::text, b.junk::text) $$) as (result json);
     result
-----------------
 {"Book" : null}
 {"Book" : null}
 {"Book" : null}
 {"Book" : null}
 {"Book" : null}
 {"Book" : null}
 {"Book" : null}
(7 rows)

psql-17.6-5432-pgsql=#

Which PG/AGE version are you using?

[wgmayer0]

Here's my solution:

SELECT jsonb_strip_nulls(
    jsonb_build_object(
        CASE WHEN a_label IS NOT NULL THEN a_label ELSE '' END, a_value,
        CASE WHEN b_label IS NOT NULL THEN b_label ELSE '' END, b_value,
        CASE WHEN c_label IS NOT NULL THEN c_label ELSE '' END, c_value,
        CASE WHEN d_label IS NOT NULL THEN d_label ELSE '' END, d_value,
        CASE WHEN e_label IS NOT NULL THEN e_label ELSE '' END, e_value,
        CASE WHEN f_label IS NOT NULL THEN f_label ELSE '' END, f_value
    )
) AS result
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)
  RETURN
    head(labels(a)) AS a_label, a.value AS a_value,
    head(labels(b)) AS b_label, b.value AS b_value,
    head(labels(c)) AS c_label, c.value AS c_value,
    head(labels(d)) AS d_label, d.value AS d_value,
    head(labels(e)) AS e_label, e.value AS e_value,
    head(labels(f)) AS f_label, f.value AS f_value
$$) AS (
    a_label text, a_value text,
    b_label text, b_value text,
    c_label text, c_value text,
    d_label text, d_value text,
    e_label text, e_value text,
    f_label text, f_value text
);

If anyone can think of ways to simplify it that would be nice. I am essentially writing the values I want in 4 different places which seems a little ridiculous. As for my version:

                                                       version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

return pg_catalog.json_build_object( might be nice if I could get it to work. Maybe I will try it in ChatGPT since I am doing something wrong

[jrgemignani]

What version of AGE?

[wgmayer0]

postgres=# SELECT extname, extversion
FROM pg_extension
WHERE extname = 'age';
 extname | extversion
---------+------------
 age     | 1.5.0
(1 row)

postgres=#

[jrgemignani]

@wgmayer0 You don't need to use head(labels()) just use label()

[wgmayer0]

Thanks

[wgmayer0]

Closest I've gotten to simplifying my solution is this:

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

But f and therefore label(f) doesn't exist so I am not sure how I can filter that out.