PostgreSQL – Creating Dynamic WHERE Clause from JSON Field

jsonpostgresqlpostgresql-9.4

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.

Best Answer

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.
Related Question