How to get the value of a specific element from JSON using PostgreSQl

Question

I have the following JSON object in PostgreSQL. I am trying to write a query to get me the details of "Elements"

{
  "ItineraryId": 0,
  "ForceSingleSearchForFlights": null,
  "ExcludeBasicFareBrand": null,
  "UserNodes": [
    {
      "User": {
        "Id": "-2",
        "GivenName": "Wiliam Never",
        "PreferredAirport": "",
        "TravelerGroupId": null,
        "ExternalId": null,
        "ExternalId2": null,
        "CheckCCFieldForTraveler": false,
        "NotificationEmailOverride": null,
        "PaxType": 0,
        "Title": "Dr",
        "MiddleName": null,
        "DateOfBirth": "1980-01-15T00:00:00Z",
        "PhoneNumber": null,
        "TsaInfo": null,
        "Source": "RequestEndPoint",
        "AllowBookingGuestTraveler": null,
        "GDSTravelerProfileContainsFirstName": false,
        "ProfilesFound": {
          "Agency": null,
          "Corporate": null,
          "Traveler": null
        },
        "UserId": "-2",
        "FirstName": "Wiliam",
        "LastName": "Never"
      },
      "Elements": null
    }
  ]
}

I have tried with multiple query options like

select itineraryintent -> 'UserNodes' ->> 'Elements' AS intent from Itinerary";

but i am always getting the following error

ERROR:  operator does not exist: text -> unknown
LINE 1: select itineraryintent -> 'UserNodes' AS intent from "Worksp...
                               ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. 

SQL state: 42883
Character: 24

From the error it looks like itineraryintent is a string type not a json type. Add the table definition, as text, to your question text. — Adrian Klaver
– Adrian Klaver, Commented Dec 26, 2023 at 16:53
select ((itineraryintent::jsonb -> 'UserNodes')->0)->>'Elements' AS intent from "Itinerary"; — Zegarek
– Zegarek, Commented Dec 26, 2023 at 16:57

TSCAmerica.com · Accepted Answer · 2023-12-26 19:11:18Z

1

Use json_array_elements like the following

SELECT 
    elements.*
FROM 
    Itinerary,
    json_array_elements(itineraryintent -> 'UserNodes') AS elements
WHERE 
    elements -> 'User' ->> 'Elements' IS NOT NULL;

answered Dec 26, 2023 at 19:11

TSCAmerica.com

5,45712 gold badges57 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get the value of a specific element from JSON using PostgreSQl

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related