How to parameterize a Postgres JSON containment query with arrays of objects?

Question

I have a JSON containment query as below. The query looks for matching objects inside arrays. This works.

The values of Name and DataType will be passed in to a .Net function, and the query built in NpgSql. I want to parameterize the values (city, string) to avoid SQL injection. How to achieve this?

As per this GitHub issue, I've tried building the json using jsonb_build_object, but I need to build arrays of objects. I'm told aggregate functions can't be used in WHERE, so that doesn't work.

So, can I build an array of objects to add the parameter values to, or is there a better way of avoiding SQL injection in this query?

We are currently using Postgres 10.18, so ideally the solution should work in that. However, we will soon upgrade to 14, so better solutions for 14 would also be of interest.

SELECT name FROM (SELECT name, fields -> 'dimensions' as dimensions
from data) x
WHERE dimensions @> '[{"Name": "city"}, {"DataType": "string"}]'

score 1 · Accepted Answer · answered Sep 08 '22 at 17:03

you can build both JSON arrays and objects without using aggregates as long as the size of each is fixed:

WHERE dimensions @> jsonb_build_array(jsonb_build_object(?,?),jsonb_build_object(?,?));

But maybe you should be using some C++ library to build the JSON and pass it already formed down to PostgreSQL. If you don't want to be dealing with JSON on the client side, you likely should not be using it on the db side either.

How to parameterize a Postgres JSON containment query with arrays of objects?

1 Answers1