Question 185034 in PostgreSQL Question posted in
The official documentation can be found here.

how to parse json array into postgresql array type?

Arrays seem really simple in PostgreSQL, but I am having trouble with JSON parsing into an array column type.

The Json data contains a property with an array value:

"problems": ["/problems/22", "/problems/31"],

The table has an array-type column like:

CREATE TABLE X(
  problems TEXT[],
  ...
);

The parsing generally works, eg.:

t.items->>'problems'  --ok

returns:

["/problems/22", "/problems/31"]

Then I tried this that does not work:

t.items->>'problems'::text[]  --error, malformed

Then I replaced in the json the [] by {} to adhere to PostgreSQL array syntax:

replace(replace(t.items->>'problems', '[', '{'), ']', '}')::text[]  --ok

While this is ‘workable’, I feel that it is a kludge. In any case, this is ugly, and not in the style of the otherwise elegant way of PostgreSQL.

How do I correctly parse the JSON into the TEXT[] array-type column?

Tags:

2

Answers


  1. 0

    If I understand correctly you need to fill the column problems by the json element problems, if that what you want then use json_array_elements_text combined with ARRAY to cast a json array into list of text values TEXT[]

    update mytable t
set problems = (SELECT ARRAY(SELECT json_array_elements_text(t.items->'problems')))

    Demo here :

    select 
  (SELECT ARRAY(
     SELECT json_array_elements_text(t.items->'problems')
  ))::text[] problems
from (
  select '{"problems": ["/problems/22", "/problems/31"]}'::json items
) t

    output:

    problems                   |
---------------------------+
{/problems/22,/problems/31}|
    Login or Signup to reply.
  2. 0

    You can use json_arrray_elements_text to iterate the array, then aggregate it back into an array:

    SELECT array_agg(value)
FROM json_array_elements_text(
  ('{"problems": ["/problems/22", "/problems/31"]}'::json)->'problems'
);

    You’d do this in a subquery if the json value comes from a relation.

    However, there is a simpler approach if your JSON object contains a property which already has the same shape as the X target table – use json_populate_record:

    SELECT *
FROM json_populate_record(NULL::X, '{"problems": ["/problems/22", "/problems/31"]}'::json);

    (online demo)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search