sql - 在sql中从json创建表

Question

在使用 sql 时，我在表中遇到了以下 json。我想将其转换为一个单独的表，其中一列用于消息，一列用于指挥官。有什么功能可以做到吗？如果有什么不同，我正在使用 postgresql。我基本上是在尝试形成一个表格，将指挥官值与其他表格中指挥官的姓名进行比较。因此，如果有一种简单的方法可以在不形成表格的情况下从儿子中提取指挥官的姓名，那也很好。

{
    "log": [
        {
            "message": "Cute dino!",
            "commander": "Sam"
        },
        {
            "message": "Cute dino!",
            "commander": "Joe"
        },
        {
            "message": "Cute dino!",
            "commander": "Bob"
        }
    ],
    "features": {
        "food": "1",
        "fuel": "0",
        "minerals": "1",
        "dinosaurs": "1"
    }
}

标签： sqljsonpostgresql

Answer 1

您可以使用jsonb_array_elements()将数组转换为行，然后将其用作 CREATE TABLE 语句的源：

create table data
as
with input (value) as (
  values ('{
      "log": [
          {
              "message": "Cute dino!",
              "commander": "Sam"
          },
          {
              "message": "Cute dino!",
              "commander": "Joe"
          },
          {
              "message": "Cute dino!",
              "commander": "Bob"
          }
      ],
      "features": {
          "food": "1",
          "fuel": "0",
          "minerals": "1",
          "dinosaurs": "1"
      }
  }'::jsonb)
) 
select t.log ->> 'message' as message,
       t.log ->> 'commander' as commander
from input
  cross join jsonb_array_elements(value -> 'log') as t(log)