„BŁĄD: zniekształcony literał tablicowy” przy użyciu json_to_record z elementem tablicy JSON w Postgres 9.4

To ładnie ilustruje problem:

Gdy kolumna b ma tekst, a nie tablicę, działają:

select * 
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') 
    as x(a int, b text, d text);

 a |         b          | d
---+--------------------+---
 1 | ["hello", "There"] |

Ale jeśli zdefiniuję bkolumnę jako tablicę, otrzymuję ten błąd:

select * 
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') 
    as x(a int, b text[], d text)

ERROR:  malformed array literal: "["hello", "There"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

Jak przekonać / przymus json_to_record(lub json_populate_record) przekonwertować tablicę JSON na tablicę Postgres typu kolumny docelowej?

Tylko niewielka zmiana w odpowiedzi Chrisa:

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM json_to_record('{"a": 1, "b": ["hello", "There"], "c": "bar"}')
AS x(a int, b text, d text);

Pomysł jest taki sam: wmasuj tablicę JSON w tablicę - w tym przypadku poprzez literał tablicowy. Oprócz nieco bardziej przejrzystego kodu (chociaż go uwielbiam, regex zwykle nie pomaga w tym względzie :), wydaje się również nieco szybszy:

CREATE TABLE jsonb_test (
    id serial,
    data jsonb
);

INSERT INTO jsonb_test (id, data)
SELECT i, format('{"a": %s, "b": ["foo", "bar"], "c": "baz"}', i::text)::jsonb 
FROM generate_series(1,10000) t(i);

SELECT a, string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b, d
FROM jsonb_test AS j, 
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

-- versus 

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM jsonb_test AS j, 
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

W tym zestawie danych i moim polu testowym wersja wyrażenia regularnego pokazuje średni czas wykonania 300 ms , podczas gdy moja wersja pokazuje 210 ms .

To może nie być najbardziej eleganckie rozwiązanie, ale rozwiąże twoje problemy ...

SELECT a,string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b,d
FROM json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
AS x(a int, b text, d text);

To całkiem proste, jak to działa:

Najpierw weź textciąg bi usuń go z przydatnych informacji. Odbywa się to za pomocą regexp_replace()as

regexp_replace(b, '\[*\"*\s*\]*','','g')

usunąć wszystkie instancje [, ", ], i wszelkie znaki odstępu, a dokładniej, aby zastąpić wszystkie wystąpienia z tych znaków ''i zastosować to globalnie, sygnalizowany za pomocą flag 'g'.

Następnie po prostu podziel ciąg na tablicę za pomocą string_to_array()as

string_to_array(your_string,',')

gdzie w tym przypadku your_stringjest po prostu wynikiem powyższego regexp_replace(). Drugi argument ','wskazywał, string_to_array()że elementy są oddzielone przecinkami.

Otrzymasz text[]pole zawierające żądane wpisy.