Insert into jsonb[] column

Question

With this table

CREATE TABLE IF NOT EXISTS actions (
    action_id UUID NOT NULL DEFAULT uuid_generate_v4(),
    inputs JSONB[] NOT NULL DEFAULT ARRAY[]::JSONB[]
)

I'm trying to insert this data

INSERT INTO actions (
    action_id,
    inputs
)
VALUES (
    '41fc94af-2f4e-424f-acde-641bb63f4b82',
    array['{"type":"string"}{"displayName":"Base OAuth URL","type":"string","description":"Base OAuth URL"}']::jsonb[]
);

But getting the error

ERROR:  invalid input syntax for type json
LINE 7:                                 array['{"type":"string"}{"di...
                                              ^
DETAIL:  Expected end of input, but found "{".
CONTEXT:  JSON data, line 1: {"type":"string"}{...

This also doesn't work

INSERT INTO actions (
    action_id,
    inputs
)
VALUES (
    '41fc94af-2f4e-424f-acde-641bb63f4b82',
    [{"type":"string"}{"displayName":"Base OAuth URL","type":"string","description":"Base OAuth URL"}]::jsonb[]
);

You are mixing types Postgres array and jsonb. Do you really want a Postgres array of jsonb objects or a JSON array of objects that is stored as jsonb? — Adrian Klaver
– Adrian Klaver, Commented Dec 9, 2021 at 21:57
Avoid jsonb[], it's hard to index and confusing to deal with — Bergi
– Bergi, Commented Dec 9, 2021 at 23:53
I agree with the other two. jsonb[] never makes sense in my opinion — user330315
– user330315, Commented Dec 10, 2021 at 6:08

SGiux · Accepted Answer · 2021-12-09 22:17:31Z

3

I didn't get you intentionr. For sure what you wrote inside array[..] is neither a single json nor an array of json.

If {"type":"string"} and {"displayName":"Base OAuth URL","type":"string","description":"Base OAuth URL"} are two different objects then you have to write:

array[
    '{"type":"string"}',
    '{"displayName":"Base OAuth URL","type":"string","description":"Base OAuth URL"}'
    ]::jsonb[]

answered Dec 9, 2021 at 22:17

SGiux

9633 gold badges16 silver badges40 bronze badges

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

1 Comment

Catfish Over a year ago

That was it. bad json. Thanks for pointing it out.

Collectives™ on Stack Overflow

Insert into jsonb[] column

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