Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- image column is a JSONB containing something like
- -- {
- -- "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"
- -- }
- CREATE TABLE mytable (id integer PRIMARY KEY, logo jsonb);
- 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"}');
- -- I wanna get the list of all "key", of both the "master" images and their "thumbnails"
- -- First approach, using a recursive CTE
- WITH RECURSIVE
- logos(i) AS (
- SELECT t.logo::jsonb
- FROM mytable AS t
- WHERE t.logo IS NOT NULL
- UNION ALL
- SELECT jsonb_array_elements(i->'thumbnails')->3 FROM logos
- )
- SELECT i->'key' FROM logos;
- -- Alternative, a union between two distinct table scans
- SELECT t.logo->'key' FROM mytable AS t WHERE t.logo IS NOT NULL
- UNION ALL
- 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