How to turn JSON array into Postgres array?

Question

I have a column data of type json that holds JSON documents like this:

{
    "name": "foo",
    "tags": ["foo", "bar"]
}

I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the array_to_string() function in theory. However, this function does not accept json input. So I wonder how to turn this JSON array into a Postgres array (type text[])?

Answer 1

Postgres 10 or newer

Postgres 10 improved the functionality of jsonb_to_record() and friends. Now even multi-dimensional JSON arrays can be converted efficiently. Quoting the release notes:

• Make json_populate_record() and related functions process JSON arrays and objects recursively (Nikita Glukhov)

  With this change, array-type fields in the destination SQL type are properly converted from JSON arrays, and composite-type fields are properly converted from JSON objects. Previously, such cases would fail because the text representation of the JSON value would be fed to array_in() or record_in(), and its syntax would not match what those input functions expect.

jsonb_to_record() requires a column definition list, where the output column name(s) must match the key(s), so move it to the FROM clause. Additional benefit: you can coerce to an element type of your choice right away.

SELECT tbl_id, t.data -> 'tags' AS jsonb_arr, j.tags AS txt_arr
FROM   tbl t
LEFT   JOIN LATERAL jsonb_to_record(t.data) AS j(tags text[]) ON true;

fiddle

Or json_to_record() for json input.

The LEFT JOIN is optional to preserve all input rows.

If you still need one, here is function with a "standard SQL" syntax (Postgres 14+):

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT tags FROM jsonb_to_record($1) AS t(tags text[]);
END;

Scales particularly well for long arrays. The key name 'tags' is hard-coded. It's possible to make the key name dynamic, but costly. Simpler with this variant:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb, _key text)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js -> _key));

Details below.

For very short arrays, string manipulation can be faster:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb, _key text)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN translate(_js ->> _key, '[]', '{}')::int[];

Postgres 9.6 or older

For multi-dimensional arrays see:

• unable to extract json column data (posgres 9.6)

Postgres 9.4 or newer

Inspired by this post, Postgres 9.4 added the missing functions to unnest JSON arrays.
_{Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!}

• json_array_elements_text(json)
• jsonb_array_elements_text(jsonb)

Use array_agg() or an ARRAY constructor to build a Postgres array (type text[]) from the resulting set of text.
Or string_agg() to build a string with a list of values (type text).

Focusing on array output (text[]), not string (text). Important difference: null elements are preserved in actual arrays. This is not possible in a string, which cannot contain null values. The true representation is an array.

Replace 'jsonb' with 'json' for type json in all following SQL code.

TLDR: Use a custom function

Encapsulate the logic in a function for repeated use:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

In Postgres 14 or later, consider the new SQL standard form:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js));

See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

Call (same for either function):

SELECT tbl_id, jsonb_array_to_text_array(data->'tags')
FROM   tbl;

• LANGUAGE sql for the simple function. (Fastest in my latest tests with Postgres 14.)

• IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow its use in index expressions.

• STRICT to return null for null input. Also: faster. The function cannot be inlined anyway because of the ARRAY constructor / the aggregate function, so STRICT cannot harm that.

• PARALLEL SAFE (in Postgres 9.6 or later!) to allow parallel execution in big queries. See:

  • When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

This function with a STRICT modifier is also as true to the original as possible in that it returns null for null input and an empty array for empty array input. Better than all of the below queries.

For completeness: use to_jsonb() for the reverse SQL array → jsonb conversion.

Various solutions, step-by-step

Immediately aggregate per row in a LATERAL or correlated subquery, then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

• How to combine ORDER BY and LIMIT with an aggregate function?
• What is the difference between LATERAL and a subquery in PostgreSQL?
• How to combine ORDER BY and LIMIT with an aggregate function?
• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Basic query, returns null for empty array or null input:

SELECT t.tbl_id, d.txt_arr
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT array_agg(d.elem) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags') AS d(elem)
   ) AS d;

Short syntax, returns null for empty array or null input:

SELECT t.tbl_id, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT array_agg(value) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags')  -- default name is "value"
   ) d;

Shorter (and faster) with ARRAY constructor, returns empty array for empty array or null input:

SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))
   ) d(txt_arr);

Even shorter (and faster) with correlated subquery, returns empty array for empty array or null input:

SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>fiddle here

All of the above preserve original order of elements.

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>fiddle here
_{Old sqlfiddle}

Related:

• Need to select a JSON array element dynamically from a PostgreSQL table

Original notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar json value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

Answer 2

PG 9.4+

The accepted answer is definitely what you need, but for the sake of simplicity here is a helper I use for this:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(p_input jsonb)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$

SELECT array_agg(ary)::text[] FROM jsonb_array_elements_text(p_input) AS ary;

$function$;

Then just do:

SELECT jsonb_array_to_text_array('["a", "b", "c"]'::jsonb);

Updated 2/23/2020 in response to comments: Comments are correct that this could be more efficient. At the time I posted there was no modularized solution offered so I offered one in earnest, if non-optimal. Since then Erwin has updated his answer with a simple and efficient function so I never updated mine. Updating it now since there is still attention coming to this answer

One more update, because this just bit me: The above function will return null if there are no values. This may not be desirable depending on your situation. Here's a function which returns an empty array if the value is not null, but still returns null if the input is null.

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array_strict(p_input jsonb)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$

SELECT 
  CASE 
    WHEN p_input IS null 
    THEN null 
    ELSE coalesce(ary_out, ARRAY[]::text[]) 
  END
FROM (
  SELECT array_agg(ary)::text[] AS ary_out
  FROM jsonb_array_elements_text(p_input) AS ary
) AS extracted;

$function$
;

Answer 3

I've tested a few options. Here is my favorite query. Suppose we have a table containing id and json field. The json field contains array, which we want to turn into pg array.

SELECT * 
FROM   test 
WHERE  TRANSLATE(jsonb::jsonb::text, '[]','{}')::INT[] 
       && ARRAY[1,2,3];

It is working anywhere and faster than others, but looks crutchy)

Firstly json array is casted as text, and then we just change square brackets to parenthesis. Finally the text is being casted as array of required type.

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::INT[];

and if you prefer text[] arrays

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::TEXT[];

Answer 4

This question was asked on the PostgreSQL mailing lists and I came up with this hackish way of converting JSON text to PostgreSQL text type via the JSON field extraction operator:

CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||$1||']')::json->>0 $$;

db=# select json_text(json_array_elements('["hello",1.3,"\u2603"]'));
 json_text 
-----------
 hello
 1.3
 ☃

Basically it converts the value into a single-element array and then asks for the first element.

Another approach would be to use this operator to extract all fields one-by-one. But for large arrays this is likely slower, as it needs to parse the whole JSON string for each array element, leading to O(n^2) complexity.

CREATE FUNCTION json_array_elements_text(json) RETURNS SETOF text IMMUTABLE LANGUAGE sql
AS $$ SELECT $1->>i FROM generate_series(0, json_array_length($1)-1) AS i $$;

db=# select json_array_elements_text('["hello",1.3,"\u2603"]');
 json_array_elements_text 
--------------------------
 hello
 1.3
 ☃

Answer 5

These few functions, taken from answers to this question, are what I'm using and they're working great

CREATE OR REPLACE FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

In each of them, by concatenating with an empty array, they handle a case that had me racking my brain for a bit, in that if you try and cast an empty array from json/jsonb without it you'll get nothing returned, instead of an empty array ({}) as you would expect. I'm certain there's some optimization for them, but they're left as is for simplicity in explaining the concept.

Answer 6

I like the translate solution mentioned so here's a tribute that doesn't interfere with the text content:

SELECT ('{' || RIGHT(LEFT( '[1]'::json::text ,-1),-1) || '}')::INTEGER[]

Answer 7

The accepted answer's solution:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

is sweet and succinct, but has a caveat for me. When invoked as:

SELECT jsonb_array_to_text_array(NULL);

It returns NULL, as desired for my use case. However, when passed a null value of a JSON property, such as:

SELECT jsonb_array_to_text_array('{"my_value_is_null": null}'::JSONB -> 'my_value_is_null');

It throws an error stating "cannot extract elements from a scalar".

Maybe I'm missing a vital piece of understanding NULL value handling, but I've tracked the issue back inside the custom function, where the null valued property is passed in as a sting of value 'null'. So my modified version:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$$SELECT CASE WHEN _js = 'null' THEN NULL ELSE ARRAY(SELECT jsonb_array_elements_text(_js)) END$$;

handles that by also returning NULL in that case, as if passed a regular, SQL NULL value.

Answer 8

I'm using composite types in Postgres 15.

Creating a new composite type

CREATE TYPE subitem AS (
   type integer,
   value real
);

Creating a table that uses the composite type

CREATE TABLE items (
    id serial not null unique primary key,
    subitems subitem[] -- An array of subitem
);

Inserting data from a JSON array with objects:

INSERT INTO items (subitems) VALUES (
  ARRAY(
    SELECT json_populate_recordset(
      null::subitem,
      '[{"type":1,"value":2},{"type":3,"value":4}]'
    )
  )
);

The first parameter to json_populate_recordset is used as the basis for how the records are to be created. It extracts the keys specified on the base, and the result after ARRAY() is an array of the correct type.

To get the data back to JSON

SELECT id, array_to_json(subitems) AS subitems
FROM items;