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.

klin · Accepted Answer · 2017-10-12 20:35:20Z

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, 2017 at 20:35

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Michaela Ervin Over a year ago

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.

klin Over a year ago

Yes, you can control results by modifying the condition.

Michaela Ervin Over a year ago

You're a lifesaver

Collectives™ on Stack Overflow

Postgresql jsonb traversal

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related