How to build query with dynamic columns with prefix?

[DrevaSergii]

I really like this library, but I found it difficult to build a highly dynamic query. My goal is to build an aggregation query to extract all possible values ​​from jsonb by fields and already selected filters.

I managed to find information how to build select with dynamic fields: sql`select ${sql(['brand', 'color'])}`

select brand, color

But how to add 'as' or 'text' prefix for the dynamic columns?

select null as brand, color

cross join lateral jsonb_to_record(attributes) as t(brand text, color text)

Here you can find more details about table and queries I need.

products table:	id	attributes
1	{"brand": "nike", "color": "red"}
2	{"brand": "levis", "color": "green"}

fields: ['brand', 'color'] filters: {}

select brand, color
from products
  cross join lateral jsonb_to_record(attributes) as t(brand text, color text)

fields: ['brand', 'color'] filters: {brand: 'nike'}

select null as brand, color
from products
  cross join lateral jsonb_to_record(attributes) as t(color text)
where attributes @> '{"brand": "nike"}'
union
select brand, null as color
from products
  cross join lateral jsonb_to_record(attributes) as t(brand text)

fields: ['brand', 'color'] filters: {brand: 'nike', color: 'red'}

select null as brand, color
from products
  cross join lateral jsonb_to_record(attributes) as t(color text)
where attributes @> '{"brand": "nike"}'
union
select brand, null as color
from products
  cross join lateral jsonb_to_record(attributes) as t(brand text)
where attributes @> '{"color": "red"}'

[porsager]

Right, you probably have to build yourself some helper functions using fragments.

I think you would probably be better off finding another way to do these queries - hopefully using more actual sql instead of dynamically building the query. Even so — I was a bit intrigued by the challenge, so here is something that might get you closer.

So for instance you could make it like this:

const join = (xs, d) => xs.reduce((acc, x, i) => sql`${ acc } ${ i ? sql`${ d } ${ x }` : x }`, sql``)

const fields = ['brand', 'color']
const filters = { brand: 'nike', color: 'red' }

const xs = await sql`${
  join(
    fields.map(f => sql`
      select ${ join([sql(f), fields.filter(x => x !== f).map(x => sql`null as ${ sql(x) }`)], sql`,`) }
      from products
      cross join lateral jsonb_to_record(attributes) as t(${ sql(f) } text)
      ${ filters[f] ? sql`where attributes @> ${ { [f]: filters[f] } }` : sql`` }
    `),
    sql`union`
  )
}`

I haven't added the empty filter case since that could simply be a ternary, but maybe this gets you closer?

[DrevaSergii]

Great, thank you! I managed to add prefixes with helper functions.

I also checked another libraries to see what they provide for creating such dynamic queries. For example, pg provides utility functions escapeIdentifier for columns and escapeLiteral for values. Haven't you thought about utility functions? It would improve developer experience.

[porsager]

sql(identifier) is the same as escapeIdentifier and sql`select ${ literal }` is the same as escapeLiteral?

The whole point of Postgres.js is to get away from string concatenation