Create dynamic WHERE clause from a JSON field

Question

We have tables like this:

people_people (
 "id" integer...
 "attributes" HSTORE
)
object_permission (
 "permissions" JSON
)

An example of an object_permission.permissions is:

{"matricule":{"authorized_keys": ["12"],
 "unauthorized_keys": ["1", "2", "20"]},
 "departement":{"authorized_keys": ["it"],
 "unauthorized_keys": ["finance", "account"]},
 "access":{"authorized_keys": ["superuser", "user", "login"],
 "unauthorized_keys": ["web"]}}

From these data, I'd like to generate the query:

SELECT * FROM people_people
WHERE
(attributes->'matricule' IN ["12"]) AND
(attributes->'departement' IN ["it"]) AND
(attributes->'access' IN ["superuser", "user", "login"]);

How to generate dynamic WHERE clause based on a JSON field? I don't want to use dynamic SQL because of the possible SQL injection.

Answer 1

Here is a small example to prove my point:

CREATE TABLE some_data (
 data hstore
);
INSERT INTO some_data
VALUES ('a=>bla'),
 ('access=>bla'),
 ('access=>user');

Now taking your JSON as an example, we can do something like this:

WITH condition_elements AS (
 SELECT key, 
 ARRAY(SELECT jsonb_array_elements_text(value->'authorized_keys')) AS value 
 FROM jsonb_each('
 {
 "matricule": {"authorized_keys":["12"],
 "unauthorized_keys": ["1", "2", "20"]},
 "departement": {"authorized_keys": ["it"],
 "unauthorized_keys":["finance", "account"]},
 "access": {"authorized_keys" : ["superuser", "user", "login"],
 "unauthorized_keys" : ["web"]}
 }'::jsonb)
)
SELECT data->key AS key, 
 value, 
 data->key=ANY(value) AS matches
 FROM some_data, 
 condition_elements;

The actual output is to describe what to expect from above:

 key │ value │ matches 
──────┼────────────────────────┼─────────
 │ {superuser,user,login} │ 
 bla │ {superuser,user,login} │ f
 user │ {superuser,user,login} │ t
 │ {12} │ 
 │ {12} │ 
 │ {12} │ 
 │ {it} │ 
 │ {it} │ 
 │ {it} │ 

If we move the necessary part to a condition, it will work nicely:

-- add some more data for testing
INSERT INTO some_data
VALUES ('departement=>bla,access=>bla'),
 ('departement=>it,access=>bla'),
 ('departement=>it,access=>login');
WITH condition_elements (... as above ...)
SELECT *
 FROM (
 SELECT data, bool_and(data->key=ANY(value)) AS full_match
 FROM some_data, condition_elements
 GROUP BY data
 ) t
 WHERE full_match;
 data │ full_match 
────────────────────────────────────────┼────────────
 "access"=>"login", "departement"=>"it" │ t
 "access"=>"user" │ t

Notes:

• It is always better to present real code in your question - like actual CREATE TABLE statements, a syntactically correct JSON snippet and a query that actually can run.
• For translating JSON arrays into PostgreSQL arrays, I used here a technique that is described in detail in Erwin Brandstetter's excellent answer.
• Check the documentation about the different JSON(B) operators and functions. It contains quite a lot of useful stuff.