CODEWITHSUNDEEP
jsonpostgresqlselectjsonobjectrequest
Rustom Contractor
Rustom Contractor

Reputation: 25

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

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

Upvotes: 0

Views: 26

Answers (1)

TSCAmerica.com
TSCAmerica.com

Reputation: 5377

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;

Upvotes: 0

Related Questions