Unwrap Postgresql Array into Rows

unwrap postgresql array into rows

Use unnest. For example:

CREATE OR REPLACE FUNCTION test( p_test text[] )
  RETURNS void AS
$BODY$
BEGIN
  SELECT id FROM unnest( p_test ) AS id;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 1;

Convert array to rows in Postgres

You could create an ARRAY from VALUES and then unnest it:

SELECT 
    unnest(ARRAY[col_a, col_b, col_c]) 
FROM 
    (VALUES('A','B','C')) AS x(col_a, col_b, col_c)

Result:

| unnest |
|--------|
|      A |
|      B |
|      C |

Edit: you could also tweak jspcal's answer by using dollar quotes ($$) like this so you can concatenate your string into the SQL statement:

  SELECT * FROM regexp_split_to_table(
    regexp_replace(
      $$('A','B','C','D','foo')$$,
      '^\(''|''\)+', '', 'g'),
      ''','''
    );

UNNEST a composite array into rows and columns in Postgres

One way I can think of, is to convert it into a jsonb array:

select item ->> 'f1' as item_name, 
       (item ->> 'f2')::int as item_id 
from jsonb_array_elements(to_jsonb(array[
                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)
              ])) t(item)

How to turn a json array into rows in postgres

I post the answer originally written by pozs in the comment section.

unnest() is for PostgreSQL's array types.

Instead one of the following function can be used:

• json_array_elements(json) (9.3+)
• jsonb_array_elements(jsonb) (9.4+)
• json[b]_array_elements_text(json[b]) (9.4+)

Example:

select * from json_array_elements('[1,true, [2,false]]')

output value

 -------------
 | 1         |
 -------------
 | true      |
 -------------
 | [2,false] |
 -------------

Here where the documentation for v9.4 can be found.

Extract ? number of JSON array objects into rows

it's unclear to me how the JSON continues in the array, but it seems you are looking for something like this:

with testdata(props) as (
 values (
  '{"searchResults": 
     [ 
       {"id":"89705","score":42,"title":"Foo"},
       {"id":"89706","score":34, "title":"bar"}
     ]}'::jsonb
 ) 
)
select x.idx, 
       x.val ->> 'id' as id,
       x.val ->> 'score' as score, 
       x.val ->> 'title' as title
from testdata, jsonb_array_elements(props -> 'searchResults') with ordinality as x (val, idx);

returns

idx | id    | score | title
----+-------+-------+------
  1 | 89705 | 42    | Foo  
  2 | 89706 | 34    | bar  

How can Postgres unpack a json array into a column then re-pack it, but preserve the data type?

demo:db<>fiddle

You are converting the elements to text when using json_array_elements_text(). Just use json_array_elements() which preserves the data type. For the window function you can cast it into text separately.

 SELECT r.id, json_agg(r.v), r.cr
  FROM
  (
    SELECT 
      t.id, 
      j.v, 
      t.cr,
      ROW_NUMBER() OVER(PARTITION BY t.id, j.v::text ORDER BY t.cr DESC) as rn
   FROM 
     t
     CROSS JOIN LATERAL json_array_elements(t.ja) j(v)
  ) r
  WHERE r.rn = 1
  GROUP BY r.id, r.cr

---

Related Topics