如何使用PostgreSQL从JSON中获取特定元素的值

j13ufse2 于 2024-01-07 发布在 PostgreSQL

关注(0)|答案(1)|浏览(156)

我在PostgreSQL中有以下JSON对象。我试图写一个查询来获取“元素”的详细信息

{
  "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
    }
  ]
}

字符串
我尝试了多个查询选项，如

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

型
但我总是得到以下错误

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

型

postgresql

来源：https://stackoverflow.com/questions/77718254/how-to-get-the-value-of-a-specific-element-from-json-using-postgresql

1条答案

按热度按时间

0g0grzrc1#

按如下方式使用json_array_elements

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

字符串

赞(0）回复(0）举报 2024-01-07

我来回答

如何使用PostgreSQL从JSON中获取特定元素的值

1条答案

相关问题

热门标签

最新问答