0

I need to write a PostgreSQL query, that filters on two columns of a database table, where the filtering condition for one of the columns depends on the specific value in the other. For example, if my columns are "Make" and "Color", I want to retrieve all of the records for:

  • Red Toyotas or blue Toyotas,
  • Red Volkswagens or green Volkswagens,
  • Blue Hondas or green Hondas

If I could hardcode the filtering criteria into my query, the WHERE clause is obviously simple enough to create:

SELECT * FROM Table
WHERE (Make = "Toyota" AND Color in ("red", "blue")) 
   OR (Make = "Volkswagen" AND Color in ("red, "green"))
   OR (Make = "Honda" AND Color in ("blue, "green"))
...

However, the query needs to be constructed so that it is configurable via JSON, and I do not know ahead of time how many Makes there will be, nor how many Colors there will be per Make, and there will almost certainly be a different number of Colors per Make. This information all needs to be delivered through a JSON variable.

I haven't been able to find the right PostgreSQL/JSON term to describe the data structure I am looking for (in Python, I'd use a list-of-lists), but is there some way I can set up a JSON variable where I can specify a set of Makes, for each Make specify a list of Colors, and then create a WHERE clause in PostgreSQL that tests for a match on any of the Makes AND the list of colors associated with that Make that was matched?

UPDATE

This query works...

SELECT x.y->'Make' as Make
from jsonb_array_elements('[
                            {"Make": "Toyota"}, 
                            {"Make": "Volkswagen"},
                            {"Make": "Honda"}
                           ]') as x(y)

...and outputs a table with the three Makes.

However, attempting to use the above in a subquery fails, with an error message from the application that calls SQL of "ERROR: Function 'JSONB_ARRAY_ELEMENTS' is not supported in this context".

SELECT * 
FROM table t
WHERE EXISTS(SELECT x.y->'Make' as Make
from jsonb_array_elements('[
                            {"Make": "Toyota"}, 
                            {"Make": "Volkswagen"},
                            {"Make": "Honda"}
                           ]') as x(y)
where x.y->>'Make' = t.Make)

Is there some reason why the jsonb_array_elements function shouldn't work in a subquery?

UPDATE 2

The difficulties with the subquery will be split into its own question

NaiveBayesian
  • 69
  • 5

1 Answers1

0

You can pass a JSON array with make and colors and use that for an EXISTS condition:

SELECT * 
FROM the_table t
where exists (select *
              from jsonb_array_elements('[
                                          {"make": "Toyota", "color": ["red", "blue"]}, 
                                          {"make": "Volkswagen", "color": ["red", "green"]}, 
                                          {"make": "Honda", "color": ["blue", "green"]} 
                                         ]') as p(item)
              where p.item ->> 'make' = t.make
                and (p.item -> 'color') ? t.color);

The parameter to the jsonb_array_elements can be passed at runtime.

Online example

  • Query is giving an error on my system (not very descriptive, just ```syntax error at or near "$1"```. Does it make a difference if the Make and model columns I'm using are numeric and not strings? – NaiveBayesian Jul 02 '21 at 18:44
  • You apparently are passing the JSON string incorrectly as a parameter. –  Jul 02 '21 at 19:33
  • I tried a simpler initial test, with only one "level" in my JSON variable, and I got this mysterious error message from the application where SQL is embedded (Dataiku's DSS): ```'JSON_ARRAY_ELEMENTS' is not supported in this context```. A quick Google search returns nothing for that particular error string, so I am think there may be an issue with the connection between PostgreSQL & Dataiku. – NaiveBayesian Jul 02 '21 at 20:06
  • Here is an example using this as a function: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=ed37b868a094adb97e14c802ab6f2f3f –  Jul 02 '21 at 20:11
  • I ran the subquery inside the EXISTS statement, including the ```jsonb_array_elements``` statement, minus the final WHERE clauses, as a top-level query, and was able to output the make/color table that was the hardwired output. However, when I put the code that had just executed back into the EXISTS statement, the ```'JSONB_ARRAY_ELEMENTS' is not supported in this context``` message reappeared. – NaiveBayesian Jul 02 '21 at 20:32
  • Update of attempted implementation in the mast post. – NaiveBayesian Jul 02 '21 at 21:06
  • Splitting the difficulties with getting the subquery to run properly into a [separate question.](https://stackoverflow.com/questions/68238053/using-json-function-with-postgresql-subqueries) – NaiveBayesian Jul 03 '21 at 16:24