Postgresql jsonb traversal

Question

I am very new to the PG jsonb field. I have for example a jsonb field containing the following

{
"RootModule": {
  "path": [
    1
  ],
  "tags": {
    "ModuleBase1": {
      "value": 40640,
      "humanstring": "40640"
    },
  "ModuleBase2": {
    "value": 40200,
    "humanstring": "40200"
    }
  },
"children": {
  "RtuInfoModule": {
    "path": [
      1,
      0
    ],
    "tags": {
      "in0": {
        "value": 11172,
        "humanstring": "11172"
      },
      "in1": {
        "value": 25913,
        "humanstring": "25913"
      }  
etc....

Is there a way to query X levels deep and search the "tags" key for a certain key.

Say I want "ModuleBase2" and "in1" and I want to get their values?

Basically I am looking for a query that will traverse a jsonb field until it finds a key and returns the value without having to know the structure.

In Python or JS a simple loop or recursive function could easily traverse a json object (or dictionary) until it finds a key.

Is there a built in function PG has to do that?

Ultimately I want to do this in django.

Edit: I see I can do stuff like

SELECT data.key AS key, data.value as value 
FROM trending_snapshot, jsonb_each(trending_snapshot.snapshot-
>'RootModule') AS data
WHERE key = 'tags';

But I must specify the the levels.

score 3 · Accepted Answer · answered Oct 12 '17 at 20:35

3

You can use a recursive query to flatten a nested jsonb, see this answer. Modify the query to find values for specific keys (add a condition in where clause):

with recursive flat (id, path, value) as (
    select id, key, value
    from my_table,
    jsonb_each(data)
union
    select f.id, concat(f.path, '.', j.key), j.value
    from flat f,
    jsonb_each(f.value) j
    where jsonb_typeof(f.value) = 'object'
)
select id, path, value
from flat
where path like any(array['%ModuleBase2.value', '%in1.value']);

 id |                       path                       | value 
----+--------------------------------------------------+-------
  1 | RootModule.tags.ModuleBase2.value                | 40200
  1 | RootModule.children.RtuInfoModule.tags.in1.value | 25913
(2 rows)

Test it in SqlFiddle.

answered Oct 12 '17 at 20:35

klin

112,967
15
204
232

Oh wow. That is really cool. Thank you so much. The idea of recursive sql is now also new to me. Works great. And I imagine editing the where clause can easily produce different desired results. – Michaela Ervin Oct 12 '17 at 20:42
Yes, you can control results by modifying the condition. – klin Oct 12 '17 at 20:46
You're a lifesaver – Michaela Ervin Oct 12 '17 at 21:07

Postgresql jsonb traversal

1 Answers1

Linked