字段A=[{“wordId“:101309,“word“:“leave“,“meaning“:“离开“,“highlightedEn“:“left“,“highlightedChinese“:“剩下“

将 JSON 数组拆成多行视图sql复制CREATE OR REPLACE VIEW your_table_flat AS SELECT t.*, -- 原表所有字段 elem-wordId AS word_id, elem-word AS word_text, elem-meaning AS meaning, elem-highlightedEn AS highlighted_en, elem-highlightedChinese AS highlighted_chinese, (elem-mapType)::int AS map_type, (elem-position)::int AS position FROM your_table t, LATERAL json_array_elements(t.字段A) AS elem;使用示例sql复制-- 查询所有拆分后的记录 SELECT * FROM your_table_flat; -- 按 wordId 过滤 SELECT * FROM your_table_flat WHERE word_id 101309; -- 聚合回一行如需要 SELECT 原表主键, json_agg( json_build_object( wordId, word_id, word, word_text, meaning, meaning ) ) AS words FROM your_table_flat GROUP BY 原表主键;如果字段A是 jsonb 类型sql复制CREATE OR REPLACE VIEW your_table_flat AS SELECT t.*, elem-wordId AS word_id, elem-word AS word_text, elem-meaning AS meaning, elem-highlightedEn AS highlighted_en, elem-highlightedChinese AS highlighted_chinese, (elem-mapType)::int AS map_type, (elem-position)::int AS position FROM your_table t, LATERAL jsonb_array_elements(t.字段A) AS elem;只拆指定字段不包含原表所有字段sql复制CREATE OR REPLACE VIEW your_table_flat AS SELECT t.id, -- 只保留原表主键 (elem-wordId)::bigint AS word_id, elem-word AS word, elem-meaning AS meaning, elem-highlightedEn AS highlighted_en, elem-highlightedChinese AS highlighted_chinese, (elem-mapType)::smallint AS map_type, (elem-position)::smallint AS position FROM your_table t, LATERAL json_array_elements(t.字段A) AS elem;LATERAL是隐式的可以省略但显式写更清晰。需要按某个字段排序或加过滤条件吗