malformed array literal - PostgreSQL

Question

I want to copy an array from jsonb field to a PostgreSQL array column:

CREATE TABLE survey_results (
    id integer NOT NULL,
    areas text[],  
    raw jsonb DEFAULT '{}'::jsonb
);

INSERT INTO survey_results (id, raw)
    VALUES (1, '{"areas": ["test", "test2"]}');

UPDATE survey_results SET areas = CAST(raw#>>'{areas}' AS text[]);

This returns me?

ERROR: malformed array literal: "["test", "test2"]" Detail: "[" must introduce explicitly-specified array dimensions.

How can I fix that?

http://sqlfiddle.com/#!17/d8122/2

I think Erwin's answer here applies to what you want to do: https://stackoverflow.com/questions/27956926/need-to-select-a-json-array-element-dynamically-from-a-postgresql-table/27957152#27957152. — Gordon Linoff, Feb 19 '18 at 13:07

Vao Tsun · Accepted Answer · 2018-02-19T13:12:28.587

14

http://sqlfiddle.com/#!17/d8122/33

json array is not self castable to postgres array. You need to either properly parse and cast it (json_array_elements, unnest, array_agg), or use some monkey hack, like:

UPDATE survey_results 
SET areas = concat('{',translate(raw#>>'{areas}','"',$$'$$),'}')::text[];

above I "prepare" json array, by changing quotes, so they would be literal, not identifier ones and prepare it to postgres array text representation '{}'

as alternative smth like:

with a as (
  select jsonb_array_elements_text(raw#>'{areas}') e from survey_results 
)
, b as (
select array_agg(e) ag from a
)
UPDATE survey_results 
SET areas = ag::text[]
FROM b;
select * from survey_results
  ;

can be used - for safer "casting"

edited Feb 19 '18 at 13:12

answered Feb 19 '18 at 13:03

Vao Tsun

47,234
13
100
132

1

In the second approach when array is empty it returns: `ERROR: cannot extract elements from a scalar`: http://sqlfiddle.com/#!17/bda1f/1 – Mateusz Urbański Feb 19 '18 at 13:33
yes - because you can't use jsonb_array_elements against not array. its not empty in fiddle - its not array at all - just a string. http://sqlfiddle.com/#!17/4d011/2 – Vao Tsun Feb 19 '18 at 13:35

score 1 · Answer 2 · answered May 16 '22 at 07:53

For Node.JS, this task could be solved approximately as follows:

// const { Client } = require('discord.js');
let user_input = 123; // for example
let fetchedInvite = await client.fetchInvite(user_input);
const features = fetchedInvite.guild.features;
// ['BANNER', /*.../* 'PREVIEW_ENABLED'];
const featuresPreparedToQuery = JSON.stringify(features)
    .replace('[', '{')
    .replace(']', '}');
console.log("featuresPreparedToQuery:", featuresPreparedToQuery);
// {'BANNER', /*.../* 'PREVIEW_ENABLED'}

malformed array literal - PostgreSQL

2 Answers2

Linked