1

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!

3
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);
3
  • 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
    Mar 23 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
    Mar 23 at 11:12
  • Sorry for the omission. Mar 23 at 11:16
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.