主页 / dba / 问题 / 287505
Accepted
daniel9x
Asked: 2021-03-23 13:34:43 +0800 CST

Postgres 11 JSONB - 如何跨所有元素获取字段的所有不同值

  • 772

我有一张桌子叫Shipments：

CREATE TABLE shipments
(
      shipmentId numeric,
      metadata jsonb
);

在metaData列内可以存在一个名为 JSON 对象的数组，stops其名称可能如下所示：

{
  "stops": [
    {
      "stopId": 1,
      "stopType": "Origin"
    },
    {
      "stopId": 2,
      "stopType": "Destionation"
    },
    {
      "stopId": 3,
      "stopType": "Transit"
    }
  ]
}

我正在尝试查询整个表并获取所有不同的stopType值。我可以通过这个简单的查询获得数组stopType中第一个索引的所有不同值：stops

select distinct metadata->'stops'->0->>'stopType' from shipments

这几乎给了我我需要的东西：

╔══════════╗
║ stopType ║
╠══════════╣
║ Origin   ║
╚══════════╝

但我想要的是：

╔══════════════╗
║ stopType     ║
╠══════════════╣
║ Origin       ║
║ Destination  ║
║ Transit      ║
╚══════════════╝

在此先感谢您的帮助！

postgresql json

2 个回答

  1. Best Answer

    使用jsonb_array_elements_text：

    SELECT DISTINCT a.e ->> 'stopType' AS "stopType"
FROM shipments AS s
   CROSS JOIN LATERAL jsonb_array_elements_text(s.metadata->'stops') AS a(e);
    • 1

  2. 当您使用 JsON 和 JSONB 时，您应该尝试了解有关该数据类型的更多信息

    CREATE TABLE shipments
(
      shipmentId numeric,
      metadata jsonb
); 

    INSERT INTO shipments (metadata) VALUES('{
  "stops": [
    {
      "stopId": 1,
      "stopType": "Origin"
    },
    {
      "stopId": 2,
      "stopType": "Destionation"
    },
    {
      "stopId": 3,
      "stopType": "Transit"
    }
  ]
}')

    select distinct metadata->'stops'->0->>'stopType' from shipments

    | ？柱子？|
| :------- |
| 产地 |

    create type allstops as ("stopId" int, "stopType" TEXT);

select "stopType" from shipments, jsonb_populate_recordset(null::allstops ,metadata->'stops')

    ✓

| 停止类型 |
| :----------- |
| 产地 |
| 目的地 |
| 过境 |

    db<>在这里摆弄

    • 0

相关问题