sql—如何在postgres中获取jsonb列的内部元素

u3r8eeie  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(342)

在我的jsonb列中有这样一个json:

  1. {
  2. "emails": [
  3. {
  4. "email": {
  5. "id": "a8399412-165e-4601-824f-a55f631ad471",
  6. "value": "test@gmail.com"
  7. }
  8. },
  9. {
  10. "email": {
  11. "id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e",
  12. "value": "test1@gmail.com"
  13. }
  14. }
  15. ],
  16. "Address": [
  17. {
  18. "address": {
  19. "id": "a8399412-165e-4601-824f-a55f631ad471",
  20. "addressLine1": "Line1"
  21. }
  22. },
  23. {
  24. "address": {
  25. "id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e",
  26. "addressLine2": "Line2"
  27. }
  28. }
  29. ],
  30. "lastName": {
  31. "id": "bc10a5a9-04ff-4a00-b167-ac3232e5cb89",
  32. "value": "LastName"
  33. },
  34. "firstName": {
  35. "id": "4ccdd400-2586-4a7f-9379-aff4d1f5d9d6",
  36. "value": "FirstName"
  37. }
  38. }

等等。我要求得到元素列表作为有限制的键和值对,我做了一个研究,尝试了postgres的不同功能,我写了下面的查询:

  1. select response.* from my_table t, jsonb_each_text(jsonb_column) as response;

如果我这样做,我只得到根元素,如email、firstname和lastname,但我也需要内部元素以及它们的值,如下所示:

  1. Key | value
  2. ------- ---------
  3. "email" : {"id": "a8399412-165e-4601-824f-a55f631ad471","value": "test@gmail.com"}
  4. "email" : {"id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e","value": "test1@gmail.com"}
  5. "lastName" : {"id": "bc10a5a9-04ff-4a00-b167-ac3232e5cb89","value": "LastName"}
  6. "firstName" : {"id": "4ccdd400-2586-4a7f-9379-aff4d1f5d9d6","value": "FirstName"}
  7. "address" : {"id": "a8399412-165e-4601-824f-a55f631ad471", "addressLine1": "Line1"}
  8. "address" : {"id": "a8399412-165e-4601-824f-a55f631ad471", "addressLine2": "Line2"}
k5ifujac

k5ifujac1#

你可以用 jsonb_array_elements() 函数,并按组合查询 UNION ALL ```
SELECT 'email' AS key, je.* ->> 'email' AS value
FROM my_table
CROSS JOIN jsonb_array_elements(jsonb_column->'emails') AS je
UNION ALL
SELECT 'address', ja.* ->> 'address'
FROM my_table
CROSS JOIN jsonb_array_elements(jsonb_column->'Address') AS ja
UNION ALL
SELECT 'lastName', (jsonb_column->'lastName')::text
FROM my_table
UNION ALL
SELECT 'firstName', (jsonb_column->'firstName' )::text
FROM my_table

  1. 演示
展开查看全部

相关问题