1

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.