Question 89481 in PostgreSQL Question posted in
The official documentation can be found here.

Merging array of array with postgres – Postgresql

I have the following jsonb :

[
    [
        {
            "id": 51003,
            "user_id": 4554,
            "sg_id": 143353
        }, {
            "id": 51004,
            "user_id": 4555,
            "sg_id": 143353
        }
    ],
    [
        {
            "id": 51005,
            "user_id": 4555,
            "sg_id": 143354
        }
    ]
]

And i’m trying to reduce it by removing the nested array level. I expect something like :

[
    {
        "id": 51003,
        "user_id": 4554,
        "sg_id": 143353
    }, {
        "id": 51004,
        "user_id": 4555,
        "sg_id": 143353
    }, {
        "id": 51005,
        "user_id": 4555,
        "sg_id": 143354
    }
]

I cannot figure out how to solve with with postgres and would appreciate any help. Cheers !

Tags:

2

Answers


  1. 0

    ->0 to access to the first element of the array which contains two objects,
    || to concat the object of the last array into the array.

    ::jsonb is to cast our varchar into json

    select myjson::jsonb->0 || to_jsonb(myjson::jsonb->1->0) as result
from mytable;

    Result :

    result

[{"id": 51003, "sg_id": 143353, "user_id": 4554}, {"id": 51004, "sg_id": 143353, "user_id": 4555}, {"id": 51005, "sg_id": 143354, "user_id": 4555}]

    Demo here

    Login or Signup to reply.
  2. 0 
    WITH j_array AS (
    SELECT
        a || lag(a) OVER () AS b
    FROM
        jsonb_array_elements('[
    [
        {
            "id": 51003,
            "user_id": 4554,
            "sg_id": 143353
        }, {
            "id": 51004,
            "user_id": 4555,
            "sg_id": 143353
        }
    ],
    [
        {
            "id": 51005,
            "user_id": 4555,
            "sg_id": 143354
        }
    ]
]'::jsonb) AS j (a))
SELECT
    *
FROM
    j_array
WHERE
    b IS NOT NULL;

 b                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------
 [{"id": 51005, "sg_id": 143354, "user_id": 4555}, {"id": 51003, "sg_id": 143353, "user_id": 4554}, {"id": 51004, "sg_id": 143353, "user_id": 4555}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search