Sample Header Ad - 728x90

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

1 vote
1 answer
1851 views
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](https://github.com/lib/pq/issues/368) , 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"}]'
Asked by Paul Guz (13 rep)
Sep 8, 2022, 11:28 AM
Last activity: Sep 8, 2022, 05:03 PM