Pg-promise: Formatting for nested properties inside JSONB

[ArtStepanyuk]

Hello and thank you for the amazing library, I was having a blast with it and it did cover all my needs.

I'm facing one issue right now, and I can't make it out of documentation if this feature is even supported. I'm trying to create a filter based on this answer

https://stackoverflow.com/questions/35779641/pg-promise-create-custom-filters-for-select-
query

But with nested data inside the JSONB column.

export class FilterSet {
  constructor(filters) {
    if (!filters || typeof filters !== 'object') {
      throw new TypeError("Parameter 'filters' must be an object.");
    }
    this.filters = filters;
    this.rawType = true; // do not escape the result from toPostgres()
  }

  toPostgres() {
    const keys = Object.keys(this.filters);
    const { as, helpers } = db.pgp();
    const s = keys.map((k) => {
      const {Column} = helpers;
      // const col1 = new Column("metadata ->> 'operationName'");
      // console.log(col1, as.name(col1) + ' = ${' + k + '}');
      return as.name("metadata ->> " +k) + ' = ${' + k + '}'
    }).join(' AND ');
    return as.format(`AND ${s}`, this.filters);
  }
}

And that's how I try to use it.

const filterSet = new FilterSet({operationName})
    const preparedQuery =  filterSet.toPostgres()

    const q = `
    SELECT
    ....
    FROM ${table}
    WHERE id=$(id)
    AND (metadata ->> 'ready' = 'true' OR metadata ->> 'ready' IS NULL)
    AND (metadata ->> 'Ready' = 'true' OR metadata ->> 'Ready' IS NULL) 
    AND $(preparedQuery:raw)
    ORDER BY updated DESC`

    return db.conn().any(q ,  {id, preparedQuery});

result query no matter what I try always ends with extra quotes

    AND (metadata ->> 'Ready' = 'true' OR metadata ->> 'Ready' IS NULL)
    AND "metadata ->> operationName" = 'myOp'
    ORDER BY updated DESC

[boromisp]

There are a few potential issues with your version:

• The as.name formatting function escapes the value as an SQL identifier.
  The correct syntax in this case would be: metadata ->> ${as.text(k)} = \${${k}}
• Your code assumes the each filter key is valid javascript variable name.
• The ->> operator returns the value as text, so you should probably cast the filter value to text.

toPostgres() {
  const { as } = db.pgp();
  const conditions = [];

  for (const key of Object.keys(this.filters)) {
    conditions.push(`metadata ->> ${as.text(key)} = ${as.text(this.filters[key])}`);
  }

  if (conditions.length > 0) {
    return ' AND ' + conditions.join(' AND ');
  }
  return '';
}

---

If all you need is equality comparison (for each key in filter: metadata[key] == filter[key]), you could use the @> operator:

toPostgres() {
  return db.pgp().as.format(' AND metadata @> $<filter:json>', this);
}

[ArtStepanyuk]

@boromisp Thank you very much! For looking into it and giving a fast reply it actually worked well! Would be really nice if examples like this would be added to the documentation

One question about format.as.text is it SQL injection proof as format.as.name?
https://vitaly-t.github.io/pg-promise/formatting.html#.name

[boromisp]

Yes, the as.text generates a valid escaped and quoted string literal that can be used as a value in a query.
Every formatting function documented on the linked page is injection proof if used in the correct context.

[vitaly-t]

For now, I will only add to avoid things like db.pgp(). You can import formatting function like this:

const {format} = require('pg-promise').as;

Also, passing this to format while inside toPostgres will result in recursive toPostgres calls, and instant stack overflow, so you need to avoid that.

Just use one of the alternative approaches...

• value repacking:

toPostgres() {
  return format(' AND metadata @> $<filter:json>', {filter: this.filter});
}

• index variables:

toPostgres() {
  return format(' AND metadata @> $1:json', [this.filter]);
}

[ArtStepanyuk]

Thank you very much guys, I got my answers. Would be very nice if those examples would be added to the documentation I'm sure it may help someone in the future.