Filter nested fields in RethinkDB rows based on multiple values

Question

I have below json in RethinkDB table

[{"pid": 0,
  "sk": [
  {
    "sid": 30,
    "et": 3
  },
  {
    "sid": 22,
    "et": 10
  },
  {
    "sid": 30,
    "et": 10
  }
  ],
"wc": [
  {
    "wid": 29,
    "et": 8
  },
  {
    "wid": 30,
    "et": 2
  },
],
"dom": [
  {
    "did": 7,
    "et": 2
  },
  {
    "did": 6,
    "et": 3
  }
],
"ex": 17,
"av": 12,
"lc": "FRA"
  }

Like this there are several thousands of rows in RethinkDB table.

My Objective is to search the data of sk, wc

For example: Input could be

"sk":[{"sid":21,"et":5},{"sid":21,"et":5}] Once filtered on above condition, the resultant dataset should again be filtered for wc field "wc":[{"wid":1,"et":7},{"wid":4,"et":5},{"wid":0,"et":7}]

I need the output records which were contained in the given input like in the table for example, sk:[{sid:2,et:8},{sid:3,et:6},{sid:3,et:7},{sid:4,et:9}] should be shown in output dataset if the input fields are below [{sid:3,et:7},{sid:4,et:9}]

I used below query when I have {sid:et} in one tuple:

 r.db('testdb').table('f_tab').
 filter(
 {
 "sk": [{"0":"8"},{"1":"5"},{"8":"5"},{"3":"8"},{"12":"4"}]
 }).filter(
 {
  "wc": [{"0":"7"},{"7":"9"},{"2":"6"},{"8":"4"},{"4":"7"}]
 }).getField('pid')

Now I have split the sid and et values for better management in server side code

Tried using r.row inside filter, but it doesn't work How can I filter based on my requirement in python ?

What is the best approach for performing nested fields search this way in perspective of performance ?

Answer 1

Does this do what you want?

r.table('f_tab').filter(
  lambda row: r.expr([{'sid': 21, 'et': 5}, ...]).set_difference(row['sk']).is_empty()
).filter(
  lambda row: r.expr([{'wid': 22, 'et': 6}, ...]).set_difference(row['wc']).is_empty()
)['pid']