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/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"
ERROR: cannot extract elements from a scalar: sqlfiddle.com/#!17/bda1f/1
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'}
