search

porsager / postgres

Postgres.js - The Fastest full featured PostgreSQL client for Node.js, Deno, Bun and CloudFlare
The Unlicense
7.32k stars 265 forks source link

Question: How to build queries with dynamic filters on JSONB field? #669

Closed tamilvendhank-helptap closed 1 year ago

tamilvendhank-helptap commented 1 year ago

Hello there,

I am trying to build a query that has dynamic JSONB field filters. I get the following error: column "metadata ->>'fieldName'" does not exists. I can see what is happening here. The filter part gets wrapped by double quotes & so it is considered as a column name. I could not find any references related to this in README page. Is it possible to build queries with dynamic JSONB filters? If it is, how do I do that?

Good day!

porsager commented 1 year ago

right, you'd need to split that op like so:

await sql`
  select
    ${ sql('metadata') }->>${ 'fieldName' } as x
  from (values ('{"fieldName": 42}'::jsonb)) as x(metadata)
`