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

Question

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!

please see minimal reproducible example and dba.meta.stackexchange.com/questions/2976/… and add the missing information — nbk
– nbk, Commented Mar 22, 2021 at 21:56
You say you want "for first index", but your example result is clearly not that. — jjanes
– jjanes, Commented Mar 23, 2021 at 1:29

Laurenz Albe · Accepted Answer · 2021-03-23 11:15:50Z

2

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

edited Mar 23, 2021 at 11:15

answered Mar 23, 2021 at 3:15

Laurenz Albe

62.8k4 gold badges58 silver badges94 bronze badges

This looks close to what I need, but I'm getting this error ERROR: operator does not exist: shipments -> unknown LINE 3: CROSS JOIN LATERAL jsonb_array_elements_text(s->'stops') ... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

daniel9x
– daniel9x

2021-03-23 11:05:30 +00:00
Commented Mar 23, 2021 at 11:05
This works! SELECT DISTINCT a.e ->> 'stopType'::text AS "stopType" FROM shipments AS s CROSS JOIN LATERAL jsonb_array_elements(metadata->'stops') AS a(e)

daniel9x
– daniel9x

2021-03-23 11:12:35 +00:00
Commented Mar 23, 2021 at 11:12
Sorry for the omission.

Laurenz Albe
– Laurenz Albe

2021-03-23 11:16:06 +00:00
Commented Mar 23, 2021 at 11:16

Add a comment |

nbk · Accepted Answer · 2021-03-23 00:08:02Z

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

Stack Exchange Network

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

2 Answers 2

Your Answer

Hot Network Questions

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

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions