Expanding a JSON array embedded in an object in Postgres 11.7

Question

Postgres 11.7. An upgrade to PG 12 (JSONPath, I know) is in planning stages, not sure when we'll get there.

I'm working on passing some data to a PL/PgSQL stored function, and am struggling to unpack an array embedded in an object. I'm dealing with some client libraries that Really Like Object as JSON Root. {[]} instead of [].

As a starting point, here's a sample that works when I get the array as the top-level element:

-- Note: jsonb instead of json may save reparsing time, if the results are reused.
-- Or so I think I heard.

with expanded_data AS (
 select *
   from jsonb_to_recordset(
          '[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
           ]')
      AS unpacked (base_text citext, base_id citext)
 )

select base_text,
       base_id

  from expanded_data

This returns the hoped-for results:

base_text           base_id
Red Large Special         1
Blue Small                5
Green Medium Special     87

This variant also works fine on a top-level array

  with expanded_data AS (
 select *
   from json_populate_recordset(
        null::record,
          '[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
           ]')
      AS unpacked (base_text citext, base_id citext)
 )

select base_text,
       base_id

  from expanded_data

What I've failed to figure out is how to get these same results when the JSON array is embedded as an element within a JSON object:

{"base_strings":[
 {"base_text":"Red Large Special","base_id":1},
 {"base_text":"Blue Small","base_id":5},
 {"base_text":"Green Medium Special","base_id":87}
]}

I've been working with the docs on the extraction syntax, and the various available functions...and haven't sorted it out. Can someone suggest a sensible strategy for expanding the embedded array elements into a rowset?

Abelisto · Accepted Answer · 2020-05-09 00:55:42Z

1

It is simple:

with expanded_data AS (
 select *
   from jsonb_to_recordset(
          '{"base_strings":[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
           ]}'::jsonb -> 'base_strings')                       -- Chages here
      AS unpacked (base_text citext, base_id citext)
 )
select base_text,
       base_id
  from expanded_data;

answered May 9, 2020 at 0:55

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Expanding a JSON array embedded in an object in Postgres 11.7

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related