Node Postgres Library Error When Searching JSON Object

Question

I am using the node-postgress package in my React project. I am trying to query my PostgreSQL database and for some reason this specific filter isn't working with their variable substitution. Am I doing something wrong?

First, my where clause is located within:

await client.query(`QUERY HERE`, [VARIABLES])

And the specific where clause causing me issues is with my object History. History is a JSON object, and contains a record, state, which is an array that contains several states. I am trying to see if a specific state code, like OH, exists in this array.

($15::text = '' OR h.history -> 'state' @> '["' || $15::text || '"]')

While this doesn't work, this does:

($15::text = '' OR h.history -> 'state' @> '["OH"]')

Can someone please help? I am very confused.

Answer 1

When dealing with JSON data, you might need to explicitly convert the JavaScript object to JSON using JSON.stringify().Write your query to use JSON.stringify() for the state array in the WHERE clause. In this, the $15::jsonb parameter will be replaced with the JSON representation of the state array.

await client.query(
  `
  SELECT *
  FROM your_table h
  WHERE ($15::text = '' OR h.history -> 'state' @> $15::jsonb)
  `,
  [JSON.stringify(["OH"])]
);

By using JSON.stringify(["OH"]), you ensure that the JavaScript array is properly converted to a JSON string, which can be used in the JSONB containment check (@>).