Search inside array of array in JSONB column in Postgresql

Question

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?

What output you are expecting? Also update your version of Postgresql Database — Akhilesh Mishra
– Akhilesh Mishra, Commented Sep 9, 2020 at 8:01

Akhilesh Mishra · Accepted Answer · 2020-09-09 08:32:07Z

1

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

answered Sep 9, 2020 at 8:32

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

Guilherme Alencar Over a year ago

I've just tested your solution and it works fine. Congrats!

Collectives™ on Stack Overflow

Search inside array of array in JSONB column in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related