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
The setup notebook is out of sync with the structure of the database.
The most important thing it adds is the flatten function (source).
dropfunction if exists flatten;
CREATE OR REPLACEFUNCTIONflatten(jsonb)
RETURNS TABLE(pathtext, object_property integer, array_item integer)
LANGUAGE 'sql'
PARALLEL SAFE
AS
$$
WITH RECURSIVE all_paths(path, "value", "object_property", "array_item") AS (
select
key "path",
value "value",
1"object_property",
0"array_item"from
jsonb_each($1)
UNION ALL (
select
case when key_value is not null then
path||'/'::text|| (key_value).key::text
else
path
end "path",
case when key_value is not null then
(key_value).value
else
array_value
end "value",
case when key_value is not null then 1 else 0 end,
case when key_value is null then 1 else 0 end
from
(selectpath,
jsonb_each(case when jsonb_typeof(value) ='object' then value else '{}'::jsonb end) key_value,
jsonb_array_elements(case when jsonb_typeof(value) ='array'and jsonb_typeof(value ->0) ='object' then value else '[]'::jsonb end) "array_value"from all_paths
) a
)
)
SELECTpath, object_property, array_item FROM all_paths;
$$;
The text was updated successfully, but these errors were encountered:
duncandewhurst
changed the title
Remove credentials and add setup notebook
Add setup notebook
Jul 30, 2024
Setup notebook
The setup notebook is out of sync with the structure of the database.
The most important thing it adds is the
flatten
function (source).The text was updated successfully, but these errors were encountered: