How to get JSONB array index

Question

I'm sure there's a simple answer to this but I just can't find it. I need to obtain JSON array elements as rows, but their index is relevant for the subsequent processing. Here's a [very simplified] example:

id  content                                                 
--  ------------------------------------------------------------------
80  {"id":"107-80", "Sections": [{"parts":5},{"parts":8},{"parts":4}]}

I need to get:

id section section_content 
-- ------- --------------- 
80 0       {"parts":5}     
80 1       {"parts":8}     
80 2       {"parts":4}

I've tried:

select
  id,
  row_number() over() - 1 as section, 
  jsonb_array_elements(content -> 'Sections') as section_content
from purchase

But the section column is not computed correctly, as shown below:

id  section  section_content 
--- -------- --------------- 
80  0        {"parts":5}     
80  0        {"parts":8}     
80  0        {"parts":4}

user330315 · Accepted Answer · 2021-06-19 21:49:58Z

4

You can use with ordinality

select p.id, s.*
from purchase p
  cross join jsonb_array_elements(p.content -> 'Sections') with ordinality as s(section_content, section)

answered Jun 19, 2021 at 21:49

user330315

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

1 Comment

The Impaler Over a year ago

Will need to study the SQL Standard ORDINALITY clause. Thanks.

nbk · Accepted Answer · 2021-06-19 21:54:09Z

1

You can use as CTE for that

CREATE TABLE purchase (
  "id" INTEGER,
  "content" JSONB
);

INSERT INTO purchase
  ("id", "content")
VALUES
  ('80', '{"id":"107-80", "Sections": [{"parts":5},{"parts":8},{"parts":4}]}');

WITH CTE AS (select
  id,
  jsonb_array_elements(content -> 'Sections') as section_content
from purchase)
SELECT
id,  ROW_NUMBER () OVER (
      PARTITION BY id)  as section, section_content FROM CTE

id | section | section_content
-: | ------: | :--------------
80 |       1 | {"parts": 5}   
80 |       2 | {"parts": 8}   
80 |       3 | {"parts": 4}

db<>fiddle here

answered Jun 19, 2021 at 21:54

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

The Impaler Over a year ago

I'm curious how the row number is assigned correcly in the absence of ORDER BY. It nevertheless works.

nbk Over a year ago

window function take the the order that is given in the CTE, that is why it is working, i was more currios why your query doesn't work, it seems, that jsonb_array_elements creates new rows without going to the pat where the window part recognozes it

Collectives™ on Stack Overflow

How to get JSONB array index

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related