malformed array literal when convetring jsonb array of jsonb items to postgres array of jsonb by jsonb_array_elements

Question

I have jsonb-array:

element_values := '[
  {
    "element_id": "a7993f3d-9256-4354-a147-5b9d18d7812b", 
    "value": true
  }, 
  {
    "element_id": "ceeb364e-bb88-4f41-9c56-9e5f4d0bc1fb", 
    "value": None
  },
  ...
]'::JSONB

And I want to convert it into array of jsonb objects: JSONB[]

I tried this method:

<<elements_n_values_relationship_create>>
DECLARE
    elements_n_values_relationship JSONB[];

BEGIN
    SELECT * FROM jsonb_array_elements(element_values) INTO elements_n_values_relationship;
    ...
END;

But I got the following error:

 ERROR:  malformed array literal: "{"value": true, "element_id": "a7993f3d-9256-4354-a147-5b9d18d7812b"}"
 DETAIL:  Unexpected array element.

Why it does not work?

@Benilson problem is not here. Even I have jsonb only with first item, I get error — Prosto_Oleg
– Prosto_Oleg, Commented Jun 14, 2022 at 12:39

gladix · Accepted Answer · 2022-06-14 12:54:02Z

2

You have to use null in place of None to make your statement work

EDIT: Try this in pgadmin or any SQL client, is is working as expected

select   jsonb_array_elements('[{
  "element_id": "a7993f3d-9256-4354-a147-5b9d18d7812b", 
  "value": true
}, 
{
  "element_id": "ceeb364e-bb88-4f41-9c56-9e5f4d0bc1fb", 
  "value": null
}]'::JSONB);

jsonb_array_elements
"{""value"":"{""value"": true, ""element_id"": ""a7993f3d-9256-4354-a147-5b9d18d7812b""}"
{ "value": null, "element_id": "ceeb364e-bb88-4f41-9c56-9e5f4d0bc1fb" }

edited Jun 14, 2022 at 12:54

answered Jun 14, 2022 at 12:25

gladix

4124 silver badges13 bronze badges

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

1 Comment

Prosto_Oleg Over a year ago

Oh, it is miss print. I copied json from python. Asyncpg will replace None to null

Collectives™ on Stack Overflow

malformed array literal when convetring jsonb array of jsonb items to postgres array of jsonb by jsonb_array_elements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related