How to get JSONB array index

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}

Answer

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)

Advertisement

Answer