Home » How to turn JSON array into Postgres array?

How to turn JSON array into Postgres array?

Solutons:

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

  • json_array_elements_text(json)
  • jsonb_array_elements_text(jsonb)

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements 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 apply ORDER BY and LIMIT in combination with an aggregate function?

Replace ‘json’ with ‘jsonb’ for jsonb in all following SQL code.

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

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

Related:

  • What is the difference between LATERAL and a subquery in PostgreSQL?

ARRAY constructor in correlated subquery:

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

Related:

  • How to apply ORDER BY and LIMIT in combination with an aggregate function?

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

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

Call:

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

db<>fiddle here

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;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Related:

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

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 …

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$
;

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
 ☃

Related Solutions

How to download package not install it with apt-get command?

Use --download-only: sudo apt-get install --download-only pppoe This will download pppoe and any dependencies you need, and place them in /var/cache/apt/archives. That way a subsequent apt-get install pppoe will be able to complete without any extra downloads....

What defines the maximum size for a command single argument?

Answers Definitely not a bug. The parameter which defines the maximum size for one argument is MAX_ARG_STRLEN. There is no documentation for this parameter other than the comments in binfmts.h: /* * These are the maximum length and maximum number of strings...

Bulk rename, change prefix

I'd say the simplest it to just use the rename command which is common on many Linux distributions. There are two common versions of this command so check its man page to find which one you have: ## rename from Perl (common in Debian systems -- Ubuntu, Mint,...

Output from ls has newlines but displays on a single line. Why?

When you pipe the output, ls acts differently. This fact is hidden away in the info documentation: If standard output is a terminal, the output is in columns (sorted vertically) and control characters are output as question marks; otherwise, the output is...

mv: Move file only if destination does not exist

mv -vn file1 file2. This command will do what you want. You can skip -v if you want. -v makes it verbose - mv will tell you that it moved file if it moves it(useful, since there is possibility that file will not be moved) -n moves only if file2 does not exist....

Is it possible to store and query JSON in SQLite?

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data . Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well. PostgreSQL has some...

Combining tail && journalctl

You could use: journalctl -u service-name -f -f, --follow Show only the most recent journal entries, and continuously print new entries as they are appended to the journal. Here I've added "service-name" to distinguish this answer from others; you substitute...

how can shellshock be exploited over SSH?

One example where this can be exploited is on servers with an authorized_keys forced command. When adding an entry to ~/.ssh/authorized_keys, you can prefix the line with command="foo" to force foo to be run any time that ssh public key is used. With this...

Why doesn’t the tilde (~) expand inside double quotes?

The reason, because inside double quotes, tilde ~ has no special meaning, it's treated as literal. POSIX defines Double-Quotes as: Enclosing characters in double-quotes ( "" ) shall preserve the literal value of all characters within the double-quotes, with the...

What is GNU Info for?

GNU Info was designed to offer documentation that was comprehensive, hyperlinked, and possible to output to multiple formats. Man pages were available, and they were great at providing printed output. However, they were designed such that each man page had a...

Set systemd service to execute after fstab mount

a CIFS network location is mounted via /etc/fstab to /mnt/ on boot-up. No, it is not. Get this right, and the rest falls into place naturally. The mount is handled by a (generated) systemd mount unit that will be named something like mnt-wibble.mount. You can...

Merge two video clips into one, placing them next to each other

To be honest, using the accepted answer resulted in a lot of dropped frames for me. However, using the hstack filter_complex produced perfectly fluid output: ffmpeg -i left.mp4 -i right.mp4 -filter_complex hstack output.mp4 ffmpeg -i input1.mp4 -i input2.mp4...

How portable are /dev/stdin, /dev/stdout and /dev/stderr?

It's been available on Linux back into its prehistory. It is not POSIX, although many actual shells (including AT&T ksh and bash) will simulate it if it's not present in the OS; note that this simulation only works at the shell level (i.e. redirection or...

How can I increase the number of inodes in an ext4 filesystem?

It seems that you have a lot more files than normal expectation. I don't know whether there is a solution to change the inode table size dynamically. I'm afraid that you need to back-up your data, and create new filesystem, and restore your data. To create new...

Why doesn’t cp have a progress bar like wget?

The tradition in unix tools is to display messages only if something goes wrong. I think this is both for design and practical reasons. The design is intended to make it obvious when something goes wrong: you get an error message, and it's not drowned in...

OpenSSH: How to end a match block

To end up a match block with openssh 6.5p1 or above, use the line: Match all Here is a piece of code, taken from my /etc/ssh/sshd_config file: # Change to no to disable tunnelled clear text passwords PasswordAuthentication no Match host 192.168.1.12...

Redirecting the content of a file to the command “echo”

You can redirect all you want to echo but it won't do anything with it. echo doesn't read its standard input. All it does is write to standard output its arguments separated by a space character and terminated by a newline character (and with some echo...