Split array of multiple data types into different arrays

Question

I have data available in postgres database on which I need to run a BI tool to have some aggregational information. Unfortunately the data is structured in the weirdest way.  I have the example json column to work with:

{"products": ["345661C1-2665-4870-9649-803B20A4B579", 1, "FE719978-253F-4763-B1B7-648B9988C5BF", 2, "F66FE491-AC06-49DD-987B-0B88CB49CEB7", 2, "5628A5A4-6030-459D-96F3-32D3C04B7F80", 3, "2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C", 1]}

I do realize the products array itself is not in the correct postgres syntax neither are the string literals.

Ideally I have a query resulting in this:

Product	Amount
345661C1-2665-4870-9649-803B20A4B579	1
FE719978-253F-4763-B1B7-648B9988C5BF	2
F66FE491-AC06-49DD-987B-0B88CB49CEB7	2
5628A5A4-6030-459D-96F3-32D3C04B7F80	3
2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C	1

Is this possible with postgres? I was looking for

a function to split arrays based on data type
a function to remove elements based on odd/even index

I would always look for a solution with code but the BI tool is the only one whitelisted for this database

Игорь Тыра · Accepted Answer · 2021-07-24 20:19:37Z

1

this will let you to unnest data from one record, or a few/all records at once

-- sample schema
create table datatable (
  id int primary key,
  data jsonb
);
-- some data
insert into datatable values
(1, '{"products": ["FE719978-253F-4763-B1B7-648B9988C5BF", 2, "F66FE491-AC06-49DD-987B-0B88CB49CEB7", 2]}'),
(2, '{"products": ["345661C1-2665-4870-9649-803B20A4B579", 1]}'),
(3, '{"products": ["5628A5A4-6030-459D-96F3-32D3C04B7F80", 3, "2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C", 1]}');
-- select
WITH pre AS (
  SELECT *, RANK()over(ORDER BY id) AS rk FROM datatable
   JOIN jsonb_array_elements_text(data->'products') WITH ORDINALITY AS v(v) ON TRUE
  WHERE id = 3 -- for specific record id
  -- remove/modify the WHERE clause for all / some records at once
)
SELECT pre1.v AS product, pre2.v::INT AS amount
 FROM pre AS pre1
 JOIN pre AS pre2
   ON pre1.ordinality % 2 = 1
  AND pre2.ordinality = 1 + pre1.ordinality 
  AND pre1.rk = pre2.rk;

edited Jul 24, 2021 at 20:19

answered Jul 24, 2021 at 19:15

Игорь Тыра

9755 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RobrechtVM Over a year ago

This just works out of the box, and the split by id is exactly what I needed afterwards!

Marth · Accepted Answer · 2021-07-24 18:15:59Z

This feels a bit hackish (and will fail if your arrays aren't in a "key/value" list format), but you could use WITH ORDINALITY to keep track of the index of each element and group by ordinality / 2 (to regroup consecutive elements together):

Schema (PostgreSQL v13)

Query #1

WITH data(arr) AS (
  VALUES (ARRAY['345661C1-2665-4870-9649-803B20A4B579', '1', 'FE719978-253F-4763-B1B7-648B9988C5BF', '2', 'F66FE491-AC06-49DD-987B-0B88CB49CEB7', '2', '5628A5A4-6030-459D-96F3-32D3C04B7F80', '3', '2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C', '1'])
)
SELECT (ARRAY_AGG(unnest ORDER BY ordinality))[1] AS product, (ARRAY_AGG(unnest ORDER BY ordinality))[2] AS amount
FROM data
CROSS JOIN LATERAL UNNEST(arr) WITH ORDINALITY
GROUP BY (ordinality-1) / 2; /* ordinality is 1-based, hence the -1 */

product	amount
345661C1-2665-4870-9649-803B20A4B579	1
FE719978-253F-4763-B1B7-648B9988C5BF	2
F66FE491-AC06-49DD-987B-0B88CB49CEB7	2
5628A5A4-6030-459D-96F3-32D3C04B7F80	3
2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C	1

View on DB Fiddle

sticky bit · Accepted Answer · 2021-07-24 18:49:16Z

1

You can use jsonb_array_elements_text() WITH ORDINALITY to get the elements of the JSON array as a table along with their ordinality. Then aggregate by grouping every two ordinalities. Use a FILTER clause to separate the odd from the even ordinalities to get the right values in the right column.

CREATE TABLE elbat
AS
SELECT '{"products": ["345661C1-2665-4870-9649-803B20A4B579", 1, "FE719978-253F-4763-B1B7-648B9988C5BF", 2, "F66FE491-AC06-49DD-987B-0B88CB49CEB7", 2, "5628A5A4-6030-459D-96F3-32D3C04B7F80", 3, "2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C", 1]}'::jsonb AS nmuloc;

SELECT max(jae.e) FILTER (WHERE jae.o % 2 = 1) AS product,
       max(jae.e) FILTER (WHERE jae.o % 2 = 0)::integer AS amount
       FROM elbat t
            CROSS JOIN LATERAL jsonb_array_elements_text(t.nmuloc->'products') WITH ORDINALITY jae
                                                                                               (e,
                                                                                                o)
       GROUP BY (jae.o + 1) / 2
       ORDER BY min(jae.o);

But you're lucky Postgres is so mighty and sophisticated that such a thing is even possible. What you really should do is stopping to abuse JSON and go the relational way. Have a table for the products like the one you want to produce from the beginning and don't store that as JSON (and even there it's the worst possible way you do that -- why an array where even elements have other meanings that odd ones, at least use an array of objects with ID an amount).

edited Jul 24, 2021 at 18:49

answered Jul 24, 2021 at 18:28

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

1 Comment

RobrechtVM Over a year ago

Thank you for showing me the WITH ORDINALITY functionality! I do realise the data is not as it should be but I don't have access to the application code, I only have the database available for reporting

Collectives™ on Stack Overflow

Split array of multiple data types into different arrays

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related