postgresql - Postgres CROSS JOIN JSON_TO_RECORD，JSON_EACH

Question

我正在尝试使用 Postgres 生成以下输出

ONE    TWO      THREE
=====================
A1     A2       A3
B1     B2       B3

来自以下 JSON

{"metadata" : { 
     "A" : { "one" : "A1" , "two" : "A2", "three" : "A3" }, 
     "B" : { "one" : "B1" , "two" : "B2", "three" : "B3" }
 }}

我有这条SQL语句

select *
  from JSON_TO_RECORD(value) as REC(ONE TEXT, TWO TEXT, THREE TEXT) 
 CROSS JOIN LATERAL JSON_EACH(
   '{"metadata" : { 
       "A" : { "one" : "A1" , "two" : "A2", "three" : "A3" }, 
       "B" : { "one" : "B1" , "two" : "B2", "three" : "B3" }
    }}'::json -> 'metadata');

当我在 PSQL 中执行时，我得到

ERROR:  column "value" does not exist
LINE 2:  from JSON_TO_RECORD(value) as REC(ONE TEXT, TWO TEXT, THREE...

我的理解是 JSON_EACH 输出一个包含 2 列、键和值的结果集，我应该能够将值传递给 JSON_TO_RECORD 运算符。我在这里缺少什么？

Answer 1

json_to_record(value) 必须在横向连接中，因为它使用 json_each() 中的 value:

select rec.*
from json_each(
   '{"metadata" : { 
       "A" : { "one" : "A1" , "two" : "A2", "three" : "A3" }, 
       "B" : { "one" : "B1" , "two" : "B2", "three" : "B3" }
    }}'::json -> 'metadata')
cross join json_to_record(value) as rec(one text, two text, three text) 

 one | two | three 
-----+-----+-------
 A1  | A2  | A3
 B1  | B2  | B3
(2 rows)