SQL join - transform row into column

Question

I'm trying to execute a SQL query to transform some rows into columns. The problem: I have more rows at the end.

Example: I have only one table in Postgres.

id	account	sub_category	jsondata (JSONB column)	date
1	1	1	{"value1":"test","value2":"test"}	01/05/2025 08:00:00
2	1	0	{"location":"A","Phone":"B"}	01/05/2025 09:00:00
3	1	1	{"value1":"test","value2":"test"}	01/05/2025 09:05:00
4	1	1	{"value1":"test","value2":"test"}	03/05/2025 09:06:00
5	1	0	{"location":"B","Phone":"C"}	04/05/2025 10:00:00
6	1	1	{"value1":"test","value2":"test"}	04/05/2025 10:15:00
7	2	1	{"value1":"test","value2":"test"}	03/05/2025 09:30:00

I'm trying to get this result:

id	Account	sub_category	jsondata (JSONB column)	date	location	phone
1	1	1	{"value1":"test","value2":"test"}	01/05/2025 08:00:00	null	null
2	1	0	{"location":"A","Phone":"B"}	01/05/2025 09:00:00	A	B
3	1	1	{"value1":"test","value2":"test"}	01/05/2025 09:05:00	A	B
4	1	1	{"value1":"test","value2":"test"}	03/05/2025 09:06:00	A	B
5	1	0	{"location":"B","Phone":"C"}	04/05/2025 10:00:00	B	C
6	1	1	{"value1":"test","value2":"test"}	04/05/2025 10:15:00	B	C
7	2	1	{"value1":"test","value2":"test"}	03/05/2025 09:30:00	null	null

Here is my query:

SELECT
    A.id,
    A.account,
    A.category,
    A.sub_category,
    A.date,
    A.jsondata,
    B.jsondata ->> 'location' AS "location",
    B.jsondata ->> 'Phone' AS "Phone",
FROM
    table1 A
LEFT JOIN 
    table1 AS B ON B.category = 0
                AND B.sub_category = 0
                AND B.account = A.account
                AND B.date = (SELECT date
                              FROM table1 AS C
                              WHERE C.category = 0
                                AND C.sub_category = 0
                                AND C.account = A.account
                                AND C.date <= A.date
                              ORDER BY
                                C.date DESC
                              LIMIT 1)
ORDER BY
    account, date;

Thanks for your help

According to this PostgreSQL 14 fiddle, your query returns your expected result (with no parasite rows at the end). Could you experiment with it to reproduce your problem (adding more test data, changing PostgreSQL's version)? — Guillaume Outters
– Guillaume Outters, Commented May 22 at 10:18
Indeed. I think this is because sometimes I have more than 1 row with category = 0 and sub_category = 0 at the same date. I have updated fiddle with this case and queries below. Thanks for you response. — user1829826
– user1829826, Commented May 22 at 12:24

Mehmet Can Turk · Accepted Answer · 2025-05-22 09:46:46Z

4

I tested the script below in PostgreSQL, and it worked. I hope this helps.

SELECT
  A.id,
  A.account,
  A.category,
  A.sub_category,
  A.date,
  A.jsondata,
  loc_data.location,
  loc_data.phone
FROM
  table1 A
LEFT JOIN LATERAL (
  SELECT
    B.jsondata ->> 'location' AS location,
    B.jsondata ->> 'Phone' AS phone
  FROM
    table1 B
  WHERE
    B.account = A.account
    AND B.category = 0
    AND B.sub_category = 0
    AND B.date <= A.date
  ORDER BY
    B.date DESC
  LIMIT 1
) loc_data ON true
ORDER BY
  A.account,
  A.date;

answered May 22 at 9:46

Mehmet Can Turk

564 bronze badges

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

Comments

Charlieface · Accepted Answer · 2025-05-22 10:49:14Z

Your primary issue is probably that there are duplicate dates. So you'd need to use a lateral limit 1 query to join.

But you can do this entirely with window functions, you don't need a join.

Unfortunately, Postgres doesn't support IGNORE NULLS syntax, otherwise you could have just done

LAST_VALUE(t.jsondata ->> 'location') IGNORE NULLS
    OVER (ORDER BY id ROWS UNBOUNDED PRECEDING)

So you need to hack it by conditionally aggregating a JSON array then indexing from the end.

WITH cte1 AS (
    SELECT
        t1.*,
        t1.jsondata -> 'location' AS location,
        t1.jsondata -> 'Phone' AS phone
    FROM
        table1 t1
)
SELECT
    t1.id,
    t1.account,
    t1.category,
    t1.sub_category,
    t1.date,
    t1.jsondata,
    jsonb_agg(t1.location) FILTER (WHERE t1.location IS NOT NULL)
         OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) ->> -1 AS location,
    jsonb_agg(t1.phone)    FILTER (WHERE t1.phone    IS NOT NULL)
         OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) ->> -1 AS Phone
FROM
    cte1 t1;

db<>fiddle

Collectives™ on Stack Overflow

SQL join - transform row into column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related