Postgresql search if exists in nested jsonb

Question

I'm new with jsonb request and i got a problem. Inside an 'Items' table, I have 'id' and 'data' jsonb. Here is what can look like a data:

[
  {
    "paramId": 3,
    "value": "dog"
  },
  {
    "paramId": 4,
    "value": "cat"
  },
  {
    "paramId": 5,
    "value": "fish"
  },
  {
    "paramId": 6,
    "value": "",
    "fields": [
      {
        "paramId": 3,
        "value": "cat"
      },
      {
        "paramId": 4,
        "value": "dog"
      }
    ]
  },
  {
    "paramId": 6,
    "value": "",
    "fields": [
      {
        "paramId": 5,
        "value": "cat"
      },
      {
        "paramId": 3,
        "value": "dog"
      }
    ]
  }
]

The value in data is always an array with object inside but sometimes the object can have a 'fields' value with objects inside. It is maximum one level deep.

How can I select the id of the items which as for example an object containing "paramId": 3 and "value": "cat" and also have an object with "paramId": 5 and "value" LIKE '%ish%'.

I already have found a way to do that when the object is on level 0

SELECT i.*
FROM items i
JOIN LATERAL jsonb_array_elements(i.data) obj3(val) ON obj.val->>'paramId' = '3'
JOIN LATERAL jsonb_array_elements(i.data) obj5(val) ON obj2.val->>'paramId' = '5'
WHERE obj3.val->>'valeur' = 'cat'
AND obj5.val->>'valeur' LIKE '%ish%';

but I don't know how to search inside the fields array if fields exists.

Thank you in advance for you help.

EDIT:
It looks like my question is not clear. I will try to make it better.

What I want to do is to find all the 'item' having in the 'data' column objects who match my search criteria. This without looking if the objects are at first level or inside a 'fields' key of an object. Again for example. This record should be selected if I search:

'paramId': 3 AND 'value': 'cat
'paramId': 4 AND 'value': LIKE '%og%'

the matching ones are in the 'fields' key of the object with 'paramId': 6 and I don't know how to do that.

You have given a detailed example of the thing you already know how to do. Please give an equally detailed example of the things you want to do but don't know how to. — jjanes, Mar 27 '20 at 19:23
What I want to do is to find the objects which are inside the 'fields' array. For example I would like to get the item if I search 'paramId'=3 and 'value'='cat' which is inside the fields array. I don't know how to search at level 0 and at level 1 especially because level 1 is not all the time here. — Léal, Mar 27 '20 at 21:42

score 1 · Accepted Answer · answered Mar 28 '20 at 09:51

This can be expressed using a JSON/Path expression without the need for unnesting everything

To search for paramId = 3 and value = 'cat'

select *
from items
where data @? '$[*] ? ( (@.paramId == 3 && @.value == "cat") || exists( @.fields[*] ? (@.paramId == 3 && @.value == "cat")) )'

The $[*] part iterates over all elements of the first level array. To check the elements in the fields array, the exists() operator is used to nest the expression. @.fields[*] iterates over all elements in the fields array and applies the same expression again. I don't see a way how repeating the values could be avoided though.

For a "like" condition, you can use like_regex:

select *
from items
where data @? '$[*] ? ( (@.paramId == 4 && @.value like_regex ".*og.*") || exists( @.fields[*] ? (@.paramId == 4 && @.value like_regex ".*og.*")) )'

Wow perfect! This is EXACTLY what I wanted. Thank you so much for your time! — Léal, Mar 28 '20 at 10:18

score 0 · Answer 2 · answered Mar 28 '20 at 08:33

For now I have found a solution but it is not really clean and I don't know how it will perform in production with 10M records.

SELECT i.id, i.data
FROM (                                                    -- A;
         select it.id, it.data, i as value
         from items it,
              jsonb_array_elements(it.data) i
         union
         select it.id, it.data, f as value
         from items it,
              jsonb_array_elements(it.data) i,
              jsonb_array_elements(i -> 'fields') f
     ) as i
WHERE (i.value ->> 'paramId' = '5'                        -- B1;
    AND i.value ->> 'value' LIKE '%ish%')
   OR (i.value ->> 'paramId' = '3'                        -- B2;
    AND i.value ->> 'value' = 'cat')
group by i.id, i.data
having COUNT(*) >= 2;                                     -- C;

A: I "flatten" the first and second level (second level is in 'fields' key)
B1, B2: These are my search criteria
C: I make sure the fields have all the criteria matching. If 3 criteria --> COUNT(*) >=3

It really doesn't look clean to me. It is working for dev purpose but I think there is a better way to do it.

If somebody have an idea Big thanks to him/her!

Postgresql search if exists in nested jsonb

2 Answers2