Node.js AGE driver unable to handle nested array in result set

[lnoir]

Hi all,

I have a query that collects and consolidates various bits of data into a derived object, but it seems to cause a problem for the AGE driver (NodeJS). Below is a contrived example you can use to replicate the issue.

Setup

SELECT * FROM ag_catalog.create_graph('nesteddemo');

SELECT create_vlabel('nesteddemo','Building');
SELECT create_vlabel('nesteddemo','User');
SELECT create_vlabel('nesteddemo','Comestible');
SELECT create_elabel('nesteddemo','likes');
SELECT create_elabel('nesteddemo','lives_in');

SELECT * FROM ag_catalog.cypher('nesteddemo', $$
   CREATE (b1:Building {id: "b001", name: 'Building One'})
   CREATE (b2:Building {id: "b002", name: 'Building Two'})
   CREATE (u1:User {id: "u001", name: 'User One'})
   CREATE (u2:User {id: "u002", name: 'User Two'})
   CREATE (u3:User {id: "u003", name: 'User Three'})
   CREATE (p1:Comestible {name: 'Pizza', flavour: 'Pepperoni'})
   CREATE (m1:Comestible {name: 'Milkshake', flavour: 'Strawberry'})
   CREATE (p2:Comestible {name: 'Hamburger'})
   CREATE (m2:Comestible {name: 'Cola'})
   CREATE (u1)-[:lives_in]->(b1)
   CREATE (u1)-[:likes]->(p1)
   CREATE (u1)-[:likes]->(m1)
   CREATE (u2)-[:lives_in]->(b2)
   CREATE (u2)-[:likes]->(p2)
   CREATE (u2)-[:likes]->(m2)
   CREATE (u3)-[:lives_in]->(b2)
   CREATE (u3)-[:likes]->(p1)
   CREATE (u3)-[:likes]->(m2)
   RETURN true
$$) as (id ag_catalog.agtype);

SET search_path = ag_catalog, "$user", public;

Query

Below is an illustrative query.

SELECT * FROM ag_catalog.cypher('nesteddemo', $$
   MATCH (b:Building)<-[:lives_in]-(u:User)-[:likes]->(c:Comestible)
   WITH {name: u.name, favouriteFoods: COLLECT(c)} AS person, u, b
   RETURN b.name, COLLECT([person]) AS people
$$) as (building ag_catalog.agtype, userdata ag_catalog.agtype);

A couple of things to note:

• The derived object (person) needs to be wrapped in an array in order to be COLLECTed
• The value of favourites in the result userdata is an array of arrays

Besides the slight inconvenience of having nested arrays, no real issue so far. All the data is returned.

However, when the above query is run via pg using the NodeJS AGE driver, userdata's top level array only contains one array where it should contain two. The result row for Building Two should contain User Two and User Three, but it only contains User Three.

This seems to me like a bug, but I'm not sure if I'm doing something wrong. If so, I'm hoping one of you can point me in the right direction. This is a significant hindrance at the moment. The actual query that's producing the described result worked well in RedisGraph and (I think) Neo4J.

Please let me know your thoughts.

[lnoir]

I haven't got to the bottom of this yet, I'm still hoping someone can help me out. Below is a Node.js script that will output the results described above, to maybe save time for anyone who wants to look at this. I'll report back if I get any further.

const { types, Pool } = require('pg');
const { setAGETypes } = require('./age-driver/dist');

class Testing {
  
  graphName;
  config;
  pool;

  constructor() {
    this.config = {
      host: 'localhost',
      port: 5432,
      user: 'postgres',
      pass: 'mypassword',
    };
    this.graphName = 'nesteddemo';
  }

  createPool() {
    return new Promise((resolve) => {
      this.pool = new Pool({
        user: this.config.user,
        host: this.config.host,
        password: this.config.pass,
        port: this.config.port,
      });
      this.pool.on('connect', client => {
        console.log('connected')
        this.init(client);
        resolve();
      });
      this.pool.connect();
    });
  }
  
  async init(client) {
    console.log('@graphName', this.graphName)
    try {
      await client.query(`
        CREATE EXTENSION IF NOT EXISTS age;
        LOAD 'age';
        SET search_path = ag_catalog, "$user", public;
      `);
      await setAGETypes(client, types);
      await client.query(`SELECT create_graph('${this.graphName}')`);
    }
    catch(err) {
      console.error(err.message);
    }
  }

  formatQuery(query, params, varsKeys) {
    let q = query;
    const agPlaceholder = params ? ', $1' : '';
    const types = (varsKeys || []).map(vk => `${vk?.var || vk} ag_catalog.agtype`).join(', ');
    const fulLStatement = `  
    SELECT * FROM ag_catalog.cypher('${this.graphName}', $$
      ${q}
    $$${agPlaceholder}) as (${types || 'n ag_catalog.agtype'});
    `;
    return fulLStatement;
  }
  
  async query(query, params, varsKeys) {
    try {
      const statement = this.formatQuery(query, params, varsKeys, '$');
      if (!this.pool) await this.createPool();
      const res = await this.pool.query(statement, params ? [params] : null);
      return {res};
    }
    catch(err) {
      console.error(err);
      return {err, res: null}; 
    }
  }

  async run() {
    const query = `
    MATCH (b:Building)<-[:lives_in]-(u:User)-[:likes]->(c:Comestible)
    WITH {name: u.name, favourites: COLLECT(c)} AS person, u, b
    RETURN b.name, COLLECT([person]) AS people
    `;
    const {res} = await this.query(query, null, ['name','people']);
    res.rows.forEach(r => {
      r.people = r.people.map(p => Object.fromEntries(p))
    });
    console.log(JSON.stringify(res.rows, null, 2))
  }
}

const testing = new Testing();

testing.run();

[eyab]

@lnoir can you please update us on this issue? Also, what is the usecase, and if you can provide more information?

[lnoir]

@eyab I didn't get any further with this and actually went with AgensGraph instead of the AGE extension as it didn't have the same issue. The code I provided should be sufficient to determine if there is a bug or not, but I've moved on from this over a year ago so have nothing to add. If it's not worth investigating then it can be closed.