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

I'm trying to get this result:

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

Answer 1

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;

Answer 2

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