Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1 (id int, account int, category int, sub_category int, jsondata jsonb, date timestamp);
insert into table1 values
	(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'),
    (9, 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":"A","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'),
    (8, 2, 0, 0, '{"location":"B","Phone":"C"}', '02/05/2025 10:00:00');

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;

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;

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;

CREATE TABLE

INSERT 0 9

id	account	sub_category	date	jsondata	location	Phone
1	1	1	2025-05-01 08:00:00	{"value1": "test", "value2": "test"}	null	null
9	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
2	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
9	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
2	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
3	1	1	2025-05-01 09:05:00	{"value1": "test", "value2": "test"}	A	B
3	1	1	2025-05-01 09:05:00	{"value1": "test", "value2": "test"}	A	B
4	1	1	2025-05-03 09:06:00	{"value1": "test", "value2": "test"}	A	B
4	1	1	2025-05-03 09:06:00	{"value1": "test", "value2": "test"}	A	B
5	1	0	2025-05-04 10:00:00	{"Phone": "C", "location": "A"}	A	C
6	1	1	2025-05-04 10:15:00	{"value1": "test", "value2": "test"}	A	C
8	2	0	2025-05-02 10:00:00	{"Phone": "C", "location": "B"}	B	C
7	2	1	2025-05-03 09:30:00	{"value1": "test", "value2": "test"}	B	C

SELECT 13

id	account	sub_category	date	jsondata	location	phone
1	1	1	2025-05-01 08:00:00	{"value1": "test", "value2": "test"}	null	null
2	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
9	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
3	1	1	2025-05-01 09:05:00	{"value1": "test", "value2": "test"}	A	B
4	1	1	2025-05-03 09:06:00	{"value1": "test", "value2": "test"}	A	B
5	1	0	2025-05-04 10:00:00	{"Phone": "C", "location": "A"}	A	C
6	1	1	2025-05-04 10:15:00	{"value1": "test", "value2": "test"}	A	C
8	2	0	2025-05-02 10:00:00	{"Phone": "C", "location": "B"}	B	C
7	2	1	2025-05-03 09:30:00	{"value1": "test", "value2": "test"}	B	C

SELECT 9

id	account	sub_category	date	jsondata	location	phone
1	1	1	2025-05-01 08:00:00	{"value1": "test", "value2": "test"}	null	null
2	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B
3	1	1	2025-05-01 09:05:00	{"value1": "test", "value2": "test"}	A	B
4	1	1	2025-05-03 09:06:00	{"value1": "test", "value2": "test"}	A	B
5	1	0	2025-05-04 10:00:00	{"Phone": "C", "location": "A"}	A	C
6	1	1	2025-05-04 10:15:00	{"value1": "test", "value2": "test"}	A	C
7	2	1	2025-05-03 09:30:00	{"value1": "test", "value2": "test"}	A	C
8	2	0	2025-05-02 10:00:00	{"Phone": "C", "location": "B"}	B	C
9	1	0	2025-05-01 09:00:00	{"Phone": "B", "location": "A"}	A	B

SELECT 9