CODEWITHSUNDEEP
sqljsonpostgresqlplpgsqljsonb
Morris de Oryx
Morris de Oryx

Reputation: 2183

Expanding a JSON array embedded in an object in Postgres 11.7

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?

Upvotes: 1

Views: 118

Answers (1)

Abelisto
Abelisto

Reputation: 15624

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;

Upvotes: 1

Related Questions