如何统一用户JSON格式：为缺失偏好字段补全null值

问题背景 #

你的数据表中client_preferences字段为JSON格式，部分用户的偏好字段缺失，需要确保所有用户包含参考用户（user_id=0001）的全部偏好字段，缺失字段设为null。参考字段包括：fav_book, fav_food, fav_holiday, fav_desert, fav_pet, fav_season。

一、检查所有用户的缺失字段 #

根据你使用的数据库类型，选择对应的SQL方法：

PostgreSQL #

1. 提取参考用户的所有偏好字段：

SELECT json_object_keys(client_preferences::json) AS required_keys
FROM your_table
WHERE user_id = '0001';

2. 批量检查每个用户的缺失字段：

WITH required_keys AS (
    SELECT json_object_keys(client_preferences::json) AS key
    FROM your_table
    WHERE user_id = '0001'
)
SELECT 
    t.user_id,
    t.user_name,
    array_agg(r.key) AS missing_keys
FROM your_table t
CROSS JOIN required_keys r
WHERE NOT (client_preferences::json ? r.key)
GROUP BY t.user_id, t.user_name
HAVING array_agg(r.key) IS NOT NULL;

MySQL #

1. 提取参考用户的偏好字段：

SELECT j.key
FROM your_table t,
     JSON_TABLE(
         JSON_KEYS(t.client_preferences),
         '$[*]' COLUMNS(key VARCHAR(50) PATH '$')
     ) j
WHERE t.user_id = '0001';

2. 批量检查缺失字段：

WITH required_keys AS (
    SELECT j.key
    FROM your_table t,
         JSON_TABLE(
             JSON_KEYS(t.client_preferences),
             '$[*]' COLUMNS(key VARCHAR(50) PATH '$')
         ) j
    WHERE t.user_id = '0001'
)
SELECT 
    t.user_id,
    t.user_name,
    GROUP_CONCAT(r.key) AS missing_keys
FROM your_table t
CROSS JOIN required_keys r
WHERE NOT JSON_CONTAINS_PATH(t.client_preferences, 'one', CONCAT('$.', r.key))
GROUP BY t.user_id, t.user_name
HAVING GROUP_CONCAT(r.key) IS NOT NULL;

二、补全缺失字段为null #

PostgreSQL #

WITH required_keys AS (
    SELECT json_object_keys(client_preferences::json) AS key
    FROM your_table
    WHERE user_id = '0001'
),
default_null_json AS (
    SELECT json_object_agg(key, NULL) AS default_json
    FROM required_keys
)
SELECT 
    t.user_id,
    t.user_name,
    default_null_json.default_json || t.client_preferences::json AS corrected_preferences
FROM your_table t
CROSS JOIN default_null_json;

注：||操作符会合并两个JSON，现有字段保留原值，缺失字段补充为null。

MySQL #

WITH required_keys AS (
    SELECT j.key
    FROM your_table t,
         JSON_TABLE(
             JSON_KEYS(t.client_preferences),
             '$[*]' COLUMNS(key VARCHAR(50) PATH '$')
         ) j
    WHERE t.user_id = '0001'
),
default_null_json AS (
    SELECT JSON_OBJECTAGG(key, NULL) AS default_json
    FROM required_keys
)
SELECT 
    t.user_id,
    t.user_name,
    JSON_MERGE_PRESERVE(d.default_json, t.client_preferences) AS corrected_preferences
FROM your_table t
CROSS JOIN default_null_json d;

注：JSON_MERGE_PRESERVE会保留原有字段值，同时补充缺失的null字段。

Python（用Pandas处理） #

如果数据量较小或需要离线处理，可使用Python脚本：

import pandas as pd
import json

# 读取数据表（假设为CSV格式，可根据实际数据源调整）
df = pd.read_csv('your_table.csv')

# 提取参考用户的所有偏好字段
reference_prefs = json.loads(df[df['user_id'] == '0001']['client_preferences'].iloc[0])
required_keys = reference_prefs.keys()

# 检查缺失字段
def get_missing_keys(pref_str):
    prefs = json.loads(pref_str)
    return [key for key in required_keys if key not in prefs]

df['missing_keys'] = df['client_preferences'].apply(get_missing_keys)

# 补全缺失字段为None（对应SQL的null）
def complete_prefs(pref_str):
    prefs = json.loads(pref_str)
    for key in required_keys:
        prefs.setdefault(key, None)
    return json.dumps(prefs)

df['corrected_preferences'] = df['client_preferences'].apply(complete_prefs)

# 输出结果
print(df[['user_id', 'user_name', 'missing_keys', 'corrected_preferences']])

内容的提问来源于stack exchange，提问作者Marcos Dias