Compare jsonb_path_query_array result with another array (ignoring ordering)

Question

I have a table table1 that looks like this:

sentence  data  
good     [{"pred": "yes", 'prob': 0.6}, {"pred": "maybe", "prob": 0.4}, {"pred": "another", "prob": 0.7}]
bad      [{"pred": "unexpected", "prob": 0.4}, {"pred": "uncool", "prob": 0.3}]

and another table table2 that looks like:

sentence  real_values  
good     ["another", "yes"]
bad      ["no"]

I want to output a boolean column signifying if the set of values in array real_values matches set of pred values from data (comparing only values where prob >= 0.5)

So, for this, the result will be:

  sentence | preds              |   real_values       |  is_match
-----------+-------
 good      | ['yes', 'another'] | ["another", "yes"]  |  true
 bad       | []                 | ["no"]              |  false   
(2 rows)

Here is what I tried so far:

SELECT sentence, 
       jsonb_path_query_array(data, '$[*] ? (@.prob >= 0.5).pred') as preds,
       table2.real_values,
(
    ARRAY(SELECT jsonb_path_query_array(data, '$[*] ? (@.prob >= 0.5).pred')) <@ ARRAY(SELECT table2.real_values)
    AND ARRAY(SELECT jsonb_path_query_array(data, '$[*] ? (@.prob >= 0.5).pred')) @> ARRAY(SELECT table2.real_values)
    ) AS is_match   
FROM table1
CROSS JOIN table2
WHERE table1.sentence = table2.sentence
GROUP BY table1.sentence, table2.real_values, table1.preds
;

AND it returns me:

  sentence | preds              |   real_values       |  is_match
-----------+-------
 good      | ['yes', 'another'] | ['another', 'yes']  |  false
 bad       | []                 | ['no']              |  false

The is_match should have been true for the first case, but is not. It does however, work when there is only 1 element in the array, not sure what is wrong with the query.

user330315 · Accepted Answer · 2021-03-01 13:23:14Z

1

You don't need to convert the JSONB values to native arrays. The @> operator also works for JSONB values.

select t1.sentence, 
       jsonb_path_query_array(t1.data, '$[*] ? (@.prob >= 0.5).pred') as preds,
       t2.real_values,
       jsonb_path_query_array(t1.data, '$[*] ? (@.prob >= 0.5).pred') @> t2.real_values as is_match
from table1 t1
  join table2 t2 on t1.sentence = t2.sentence

answered Mar 1, 2021 at 13:23

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare jsonb_path_query_array result with another array (ignoring ordering)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related