PostgreSQL JSONB – Get All Distinct Values of a Field

arrayjsonpostgresql

I have a table called Shipments:

CREATE TABLE shipments
(
      shipmentId numeric,
      metadata jsonb
);

Inside the metaData column there can exist an array of JSON objects named stops which might look something like this:

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

I'm trying to query across the table and get all the distinct stopType values. I can get all the distinct stopType values for the first index in the stops array via this simple query:

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

This almost gives me what I need:

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

But what I want is:

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

Thanks in advance for your help!

Best Answer

Use 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);
Related Question