-- 基本用法
SELECT json_merge_patch_diff(
    '{"a":1,"b":2,"c":3}',
    '{"a":1,"b":99,"d":4}'
) AS patch;
-- 结果: {"c":null,"b":99,"d":4}
-- a 未变所以省略，b 已修改，c 被删除（标记为 null），d 是新字段

SELECT json_merge_patch_diff(
    '{"user":{"name":"Alice","age":30}}',
    '{"user":{"name":"Alice","age":31}}'
) AS patch;
-- 结果: {"user":{"age":31}}
-- 只有变更的路径才会出现在补丁中

-- json_merge_patch: null 删除键
SELECT json_merge_patch('{"a":1,"b":2}', '{"b":null}');
-- 结果: {"a":1}

-- json_deep_merge: null 保留原值
SELECT json_deep_merge('{"a":1,"b":2}', '{"b":null}');
-- 结果: {"a":1,"b":2}

-- 两个上游系统各自只更新部分字段
SELECT json_deep_merge(
    '{"columnName":"user_id","parentColumn":null}',
    '{"columnName":null,"parentColumn":"accounts.id"}'
) AS merged;
-- 结果: {"columnName":"user_id","parentColumn":"accounts.id"}
-- 每个系统不知道的值用 null 表示，deep_merge 会保留原始值

SELECT json_deep_merge(
    '{"a":1}',
    '{"a":null}',    -- 跳过
    '{"a":2}'        -- 覆盖
);
-- 结果: {"a":2}

SELECT json_normalize('{"z":1,"a":2,"m":3}');
-- 结果: {"a":2,"m":3,"z":1}

SELECT json_normalize('{"c":{"b":{"z":1,"a":2},"a":3},"a":4}');
-- 结果: {"a":4,"c":{"a":3,"b":{"a":2,"z":1}}}

-- 用于去重：相同内容但不同键顺序的 JSON 会产生相同的 hash
SELECT md5(json_normalize('{"z":1,"a":2}')) = 
       md5(json_normalize('{"a":2,"z":1}')) AS same_hash;
-- 结果: true

SELECT json_strip_nulls('{"a":1,"b":null,"c":null,"d":2}');
-- 结果: {"a":1,"d":2}

SELECT json_strip_nulls('{"a":{"x":1,"y":null},"b":2}');
-- 结果: {"a":{"x":1},"b":2}

-- 数组元素不受影响（null 是有效的数组元素）
SELECT json_strip_nulls('{"a":[{"x":1,"y":null},{"z":null}]}');
-- 结果: {"a":[{"x":1,"y":null},{}]}

-- 创建表
CREATE TABLE catalog (id INTEGER, state JSON);
CREATE TABLE incoming_events (id INTEGER, event JSON);

-- 插入当前状态
INSERT INTO catalog VALUES (
    1,
    '{"typeName":"Column","name":"user_id","description":"primary key",
      "dataType":"BIGINT","ownerEmail":"[email protected]"}'
);

-- 插入 incoming 事件（部分字段为 null）
INSERT INTO incoming_events VALUES (
    1,
    '{"name":"user_id","description":"primary key for the users table",
      "dataType":"BIGINT","ownerEmail":null,"team":null}'
);

-- 完整对账流程（单条 SQL）
WITH cleaned AS (
    -- 步骤1：清理 null 占位符
    SELECT json_strip_nulls(event) AS clean_event
    FROM incoming_events
    WHERE id = 1
),
merged AS (
    -- 步骤2：深度合并（缺失字段保留原值）
    SELECT json_deep_merge(c.state, cl.clean_event) AS merged_state
    FROM catalog c, cleaned cl
),
patched AS (
    -- 步骤3：计算最小补丁
    SELECT json_merge_patch_diff(c.state, m.merged_state) AS patch
    FROM catalog c, merged m
)
-- 步骤4：应用补丁并计算规范化哈希
SELECT 
    patch,
    md5(json_normalize(json_merge_patch(c.state, p.patch))) AS content_hash
FROM catalog c, patched p
WHERE c.id = 1;

# 安装 DuckDB v2.0-dev preview
pip install duckdb --pre

import duckdb
con = duckdb.connect(":memory:")
con.execute("LOAD json;")

# 测试 json_normalize
result = con.execute("SELECT json_normalize('{\"z\":1,\"a\":2}');").fetchone()
print(result)  # ('{"a":2,"z":1}',)