Postgres 11 JSONB - How to get all distinct values of a field across all elements 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:
{
  &quot;stops&quot;: [
    {
      &quot;stopId&quot;: 1,
      &quot;stopType&quot;: &quot;Origin&quot;
    },
    {
      &quot;stopId&quot;: 2,
      &quot;stopType&quot;: &quot;Destionation&quot;
    },
    {
      &quot;stopId&quot;: 3,
      &quot;stopType&quot;: &quot;Transit&quot;
    }
  ]
}

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-&gt;'stops'-&gt;0-&gt;&gt;'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!
Use jsonb_array_elements_text:
SELECT DISTINCT a.e -&gt;&gt; 'stopType' AS &quot;stopType&quot;
FROM shipments AS s
   CROSS JOIN LATERAL jsonb_array_elements_text(s.metadata-&gt;'stops') AS a(e);