CODEWITHSUNDEEP
postgresqljsonb
kimi1990mp
kimi1990mp

Reputation: 131

Search inside array of array in JSONB column in Postgresql

I have a JSONB column in my PostgreSQL database. The data looks like this:

{
   "cars": 
      [ 
        {
          "id": 1,
          "brand": "BMW"
          "parts": 
            [
              {
                "partId": 5,
                "type": "battery"
              }
            ]
        },
        {
          "id": 2,
          "brand": "Mercedes"
          "parts": 
            [
              {
                "partId": 5,
                "type": "battery"
              },
              {
                "partId": 6,
                "type": "engine"
              }
            ]
        }
     ]
}

Is there any way that I can search for all cars that have a part with type "battery"? How can I search inside of cars array and then inside of the parts array of each car element?

Upvotes: 2

Views: 103

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

As it's not clear in your question that what output you want. So I am assuming that you want id and brand name in output:

so you try this:

select distinct x.y->>'id', x.y->>'brand' 
from test 
cross join lateral jsonb_array_elements(data->'cars') x(y)
cross join lateral jsonb_array_elements(x.y->'parts') a(b)
where a.b->>'type'='battery'

DEMO

Upvotes: 1

Related Questions