You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
create a table with one of the columns that is a FK to the PKey of that table (so like a "parent record" relationship)
create a json (not jsonb) type column
run in subset mode (not exactly sure if you need to have the subset where clause link back or not)
Bad Query:
WITH RECURSIVE related AS (
SELECT
public.table_name.id,
public.table_name.parent_record_id,
public.table_name.json_col
FROM
public.table_name
UNION
(
SELECT
public.table_name.id,
public.table_name.parent_record_id,
public.table_name.json_col
FROM
public.table_name
INNER JOIN related ON (
public.table_name.id = related.parent_record_id
)
)
)
SELECT
DISTINCT id,
parent_record_id,
json_col
FROM
related;
Working Query:
WITH RECURSIVE related AS (
SELECT
public.table_name.id,
to_jsonb(public.table_name.json_col) as im,
public.table_name.parent_id
FROM
public.table_name
UNION
(
SELECT
public.table_name.id,
to_jsonb(public.table_name.json_col) as im,
public.table_name.parent_id
FROM
public.table_name
INNER JOIN related ON (
public.table_name.id = related.parent_id
)
)
)
SELECT
DISTINCT id,
im,
parent_id
FROM
related
See this thread for more details:
https://discord.com/channels/1171894401140797591/1235948472784523284
Repro steps:
steps that should repro it:
json
(notjsonb
) type columnBad Query:
Working Query:
From SyncLinear.com | NEOS-1075
The text was updated successfully, but these errors were encountered: