Postgres JSONB Query searching dynamic path-CodePudding

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

CodePudding user response：

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.