Postgres 11 JSONB – How to get all distinct values of a field across all elements

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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);

Method 2

When you are using JsON and JSONB, you should try to learn more about that datatype

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
| ?column? |
| :------- |
| Origin   |
create type allstops as ("stopId" int, "stopType" TEXT);

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

| stopType     |
| :----------- |
| Origin       |
| Destionation |
| Transit      |

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply