Postgres JSONB Query searching dynamic path

Question

I have a Postgres json field products with jsonb like this

{
  "user": "7871425f-abb4-42c6-8e4d-36c99ec2f67c",
  "product": {
    "ef688ed3-12c9-4e22-af5f-32a7b32db628": {
      "category": "basic"
    },
    "8fbef749-42dd-4a40-a98a-ebe7b54c4388": {
      "category": "advanced"
    },
    "d475185f-d014-4c60-aebd-cecaed7df550": {
    }
  }
}

I am trying to extract category for each product to get something like

"ef688ed3-12c9-4e22-af5f-32a7b32db628": "basic"
"8fbef749-42dd-4a40-a98a-ebe7b54c4388": "advanced"
"d475185f-d014-4c60-aebd-cecaed7df550": NULL

Any help highly appreciated

Frank Heikens · Accepted Answer · 2022-08-05 15:32:02Z

2

jsonb_each() would do the trick:

SELECT  key
    ,       value->>'category'
FROM    jsonb_each('{
  "user": "7871425f-abb4-42c6-8e4d-36c99ec2f67c",
  "product": {
    "ef688ed3-12c9-4e22-af5f-32a7b32db628": {
      "category": "basic"
    },
    "8fbef749-42dd-4a40-a98a-ebe7b54c4388": {
      "category": "advanced"
    },
    "d475185f-d014-4c60-aebd-cecaed7df550": {
    }
  }
}'::jsonb ->'product');

The sort order is up to you.

answered Aug 5, 2022 at 15:32

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Collectives™ on Stack Overflow

Postgres JSONB Query searching dynamic path

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