Advertisement
Guest User

WITH RECURSIVE or double scan?

a guest
Nov 15th, 2019
135
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- image column is a JSONB containing something like
  2. -- {
  3. --   "key": "16c536a1-b552-4e13-b171-2f5370e4627f",
  4. --   "width": 367,
  5. --   "height": 239,
  6. --   "length": 48910,
  7. --   "extension": ".png",
  8. --   "timestamp": "1563904541.1229434",
  9. --   "thumbnails": [
  10. --     [
  11. --       340,
  12. --       221,
  13. --       0.92643,
  14. --       {
  15. --         "key": "7203e420-cb1e-457d-bd70-01412137a2e5",
  16. --         "width": 340,
  17. --         "height": 221,
  18. --         "length": 9081,
  19. --         "extension": ".jpg",
  20. --         "timestamp": "1563944400.5420659",
  21. --         "content_type": "image/jpeg",
  22. --         "reproducible": false
  23. --       }
  24. --     ],
  25. --     [
  26. --       300,
  27. --       200,
  28. --       0.92643,
  29. --       {
  30. --         "key": "2f80bf8c-07bb-11ea-b414-3085a99ccac7",
  31. --         "width": 300,
  32. --         "height": 200,
  33. --         "length": 8025,
  34. --         "extension": ".jpg",
  35. --         "timestamp": "1563944400.5420659",
  36. --         "content_type": "image/jpeg",
  37. --         "reproducible": false
  38. --       }
  39. --     ]
  40. --   ],
  41. --   "content_type": "image/png",
  42. --   "reproducible": false,
  43. --   "original_filename": "/tmp/tmpoqwnnn3x/emblems/logo.jpg"
  44. -- }
  45.  
  46. CREATE TABLE mytable (id integer PRIMARY KEY, logo jsonb);
  47. INSERT INTO mytable (id, logo) VALUES (1, '{"key": "16c536a1-b552-4e13-b171-2f5370e4627f", "width": 367, "height": 239, "length": 48910, "extension": ".png", "timestamp": "1563904541.1229434", "thumbnails": [[340, 221, 0.92643, {"key": "7203e420-cb1e-457d-bd70-01412137a2e5", "width": 340, "height": 221, "length": 9081, "extension": ".jpg", "timestamp": "1563944400.5420659", "content_type": "image/jpeg", "reproducible": false}], [300, 200, 0.92643, {"key": "2f80bf8c-07bb-11ea-b414-3085a99ccac7", "width": 300, "height": 200, "length": 8025, "extension": ".jpg", "timestamp": "1563944400.5420659", "content_type": "image/jpeg", "reproducible": false}]], "content_type": "image/png", "reproducible": false, "original_filename": "/tmp/tmpoqwnnn3x/emblems/logo.jpg"}');
  48.  
  49. -- I wanna get the list of all "key", of both the "master" images and their "thumbnails"
  50.  
  51. -- First approach, using a recursive CTE
  52. WITH RECURSIVE
  53.   logos(i) AS (
  54.     SELECT t.logo::jsonb
  55.       FROM mytable AS t
  56.      WHERE t.logo IS NOT NULL
  57.            
  58.     UNION ALL
  59.            
  60.     SELECT jsonb_array_elements(i->'thumbnails')->3 FROM logos
  61.   )
  62. SELECT i->'key' FROM logos;
  63.                        
  64. -- Alternative, a union between two distinct table scans
  65. SELECT t.logo->'key' FROM mytable AS t WHERE t.logo IS NOT NULL
  66.  
  67. UNION ALL
  68.                                              
  69. SELECT jsonb_array_elements(t.logo->'thumbnails')->3->'key' FROM mytable AS t WHERE t.logo IS NOT NULL;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement