How to parse jsonb field into columns without keys in PostgreSQL

Question

I have table login( id int, meta_skills jsonb), but the jsonb is not stored in key-value pairs.

The data in the jsonb field look like

{
  "Cat1": [
    {
      "Skill_1": 2,
      "Skill_2": 2,
      "Skill_3": 2,
      "Skill_4": 2,
      "Skill_5": 2
    }
  ],
  "Cat2": [
    {
      "Skill_1": 3,
      "Skill_2": 2,
      "Skill_3": 3
    }
  ],
  "Cat3": [
    {
      "Skill_1": 2,
      "Skill_2": 2,
      "Skill_3": 2,
      "Skill_4": 2
    }
  ]
}

The skills values are random values. and I want to prepare the data in following format

enter image description here

@LaurenzAlbe Removed disturbing comma, some parser don't care, I think. — Turo
– Turo, Commented Apr 13, 2021 at 11:58

Laurenz Albe · Accepted Answer · 2021-04-13 12:10:58Z

2

You have to unnest the levels:

SELECT login.id,
       u1.category,
       u3.skill,
       u3.level
FROM login
   CROSS JOIN LATERAL jsonb_each(login.meta_skills) AS u1(category,v)
   CROSS JOIN LATERAL jsonb_array_elements(u1.v) AS u2(v)
   CROSS JOIN LATERAL jsonb_each(u2.v) AS u3(skill, level);

answered Apr 13, 2021 at 12:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Shailesh Over a year ago

Thank you so much. This is absolutely working and first time I have seen the join with JSON functions.

Collectives™ on Stack Overflow

How to parse jsonb field into columns without keys 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