There is a table 1 with string fields. f1, f2, f3, f4, ....

There is a table 2 with json arrays f1, f2, f3, f4, ...

Sample data:

     Table 1: - Line 1 f1 = 'AB' f2 = 'CD' f3 = 'AD' f4 = 'P' - Line 2 f1 = 'AA' f2 = 'BB' f3 = 'CC' f4 = null Table 2: f1 = ['AA', 'BB'] f2 = ['CD', 'BB'] f3 = ['AD', 'CC', 'BB'] f4 = ['AB', 'CD', null]

I want to find a row from table 1, knowing the row from table 2. Of course, I can do this using php, but I want to get a result in one query. The search should be done like this: where t1 in ['AA', 'BB']

Matches must be counted for all columns. I can not figure out how to build a query and whether null will work.

  • Samples of the filled tables and what exactly should be found are given in the question (i.e. create table and insert filling tables), it is possible on sqlfiddle.com. In general, I’d look at the list of postgresql functions that work with json in Google and suspect that json_array_elements exactly what you need - Mike
  • I json for example cited. In short, I will have one table with data and one table with conditions. I want to find data from the first table on the conditions of the second and vice versa. I do not know how best to organize. The situation is complicated by the fact that this query should not be executed for too long. - ilyaplot 2:41 pm
  • It is also alarming that select 1 where null in (null) does not work, so the question remains about finding null values. - ilyaplot
  • Another addition. select '"BB"' in (select json_array_elements('["AA","BB","CC",null]')::text); returns true, but there should be no quotes. Without quotes false - ilyaplot
  • json_array_elements_text Helped - ilyaplot

0