How to convert a postgresql JSONB column into array column

Question

I have a column in the table which stores polygon points in jsonb,

position
---------------
[{"x": 455, "y": -3165},{"x": 455,"y": 50},  {"x": 5708,"y": 50}]

I want to turn the jsonb into array, like this:

position_array
-----------------
((455, -3165),(455, 50), (5708, 50))

I can't figout a simple way to do this. Pls help

Answer 1

You should use the jsonb_to_recordset(jsonb) function, from the official PostgreSQL docs

To quote the docs:

Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause

In this case, we need to apply jsonb_to_recordset to the actual jsonb itself. This will allow postgres to think about your jsonb array as a set of rows that follow a composite type definition, which is defined by AS (x int, y int) in your case. This tells postgres that, once it has finished transforming the jsonb into a recordset, it should pluck out the x and y keys in your jsonb and treat them as integers.

You can read more about composite types here

That looks like this (I simply copied and pasted your raw jsonb in here):

jsonb_to_recordset('[{"x": 455, "y": -3165},{"x": 455,"y": 50},  {"x": 5708,"y": 50}]') AS position(x int, y int);

If you run:

SELECT * FROM jsonb_to_recordset('[{"x": 455, "y": -3165},{"x": 455,"y": 50},  {"x": 5708,"y": 50}]') AS (x int, y int);

You'll see that it gives you 3 rows with columns x and y containing the expected values from each object in your jsonb.

Next, you're going to want to use array_agg to turn this into your final desired array. However, you'll need to specify that what you want to aggregate is actually array[x, y] -- this will effectively aggregate an array of arrays with shape {x, y}, where x and y are both ints coming from your composite type AS (x int, y int).

Putting it all together, this should give you the desired result:

SELECT array_agg(array[x, y]) as position_array FROM jsonb_to_recordset('[{"x": 455, "y": -3165},{"x": 455,"y": 50},  {"x": 5708,"y": 50}]') AS (x int, y int);

Outputting:

{{455,-3165},{455,50},{5708,50}}

Just a quick note: in Postgres, arrays are defined with { instead of ( :)

Answer 2

step-by-step demo:db<>fiddle

SELECT 
    array_agg(                            -- 4
        ARRAY[                            -- 3
            (elems -> 'x')::int,
            (elems -> 'y')::int           -- 2
        ]
    )
FROM
    t,
    jsonb_array_elements(data) as elems   -- 1

1. Extract all array elements into one row per element
2. Fetch x/y values
3. Aggregate x/y into an array
4. Aggregate these arrays into one global array