Skip to content
Advertisement

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:

This returns the hoped-for results:

This variant also works fine on a top-level array

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:

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?

Advertisement

Answer

It is simple:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement