json_agg example

See https://til.simonwillison.net/sqlite/related-rows-single-query

Owned by simonw, visibility: Public

SQL query
select
  blog_entry.id,
  title,
  slug,
  created,
  coalesce(json_agg(json_build_object(blog_tag.id, blog_tag.tag)) filter (
    where
      blog_tag.tag is not null
  ), json_build_array()) as tags
from
  blog_entry
  left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
  left join blog_tag on blog_entry_tags.tag_id = blog_tag.id
group by
  blog_entry.id
order by
  blog_entry.id

Results were truncated

id title slug created tags
1 WaSP Phase II webStandards 2002-06-12 01:44:12+00:00 
[]
2 Blogging aint easy bloggingAintEasy 2002-06-12 02:02:41+00:00 
[]
3 Todo list todoList 2002-06-12 02:47:03+00:00 
[]
4 Netscape 4 is 5 years old t1024344375 2002-06-12 02:59:49+00:00 
[]
5 Webdesign-L ablaze! t1024344376 2002-06-12 14:29:41+00:00 
[]
6 Day 3: Bill day3Bill 2002-06-12 15:17:24+00:00 
[]
7 Charity and Amazon charityAndAmazon 2002-06-13 03:21:53+00:00 
[]
8 Tree from unordered list treeFromUnorderedList 2002-06-13 03:30:07+00:00 
[]
9 Mozilla alpha mozillaAlpha 2002-06-13 09:53:53+00:00 
[]
10 Hixie on WaSP hixieOnWaSP 2002-06-13 15:36:22+00:00 
[]
11 Hixie replies hixieReplies 2002-06-13 19:13:56+00:00 
[]
12 More FuzzyBlog stuff moreFuzzyBlog 2002-06-13 19:38:39+00:00 
[]
13 Hixie replies again hixieRepliesAgain 2002-06-14 02:58:09+00:00 
[]
14 I validate again iValidateAgain 2002-06-14 03:13:44+00:00 
[]
15 Zend Engine 2 alpha zendEngine2Alpha 2002-06-14 03:52:53+00:00 
[]
16 Sex tips for Geeks sexTips 2002-06-14 03:55:32+00:00 
[]
17 Day 4: Lillian day4Lillian 2002-06-14 04:10:34+00:00 
[]
18 Blog added to the OED blogAddedToOED 2002-06-14 04:16:07+00:00 
[]
19 Meta weblog API metaWeblogAPI 2002-06-14 07:02:13+00:00 
[
  {
    "2618": "metaweblogapi"
  },
  {
    "4621": "xmlrpc"
  },
  {
    "5383": "pythoncard"
  }
]
20 Mark explains all markExplainsAll 2002-06-14 17:56:08+00:00 
[]
21 CSS panic guide cssPanicGuide 2002-06-15 01:40:51+00:00 
[]
22 Uni year ends uniYearEnds 2002-06-15 10:00:30+00:00 
[]
23 Google already googleAlready 2002-06-15 10:44:06+00:00 
[]
24 Meg on blogging megOnBlogging 2002-06-15 11:01:47+00:00 
[
  {
    "2584": "meghourihan"
  },
  {
    "431": "blogging"
  }
]
25 Python iterators pythonIterators 2002-06-15 11:36:58+00:00 
[
  {
    "3350": "python"
  }
]
26 Learning from smart tags learningFromSmartTags 2002-06-15 12:45:11+00:00 
[]
27 Pure CSS popups pureCssPopups 2002-06-15 13:03:59+00:00 
[]
28 User Agent list userAgentList 2002-06-15 14:25:27+00:00 
[]
29 JSRS 2.1 released jsrs21Released 2002-06-15 20:18:29+00:00 
[]
30 The nature of blogging theNatureOfBlogging 2002-06-15 20:42:33+00:00 
[]
31 Anil Dash does Amazon amilDashDoesAmazon 2002-06-15 21:11:15+00:00 
[]
32 Has Paul finished? hasPaulFinished 2002-06-15 21:15:12+00:00 
[]
33 Meetup Launches meetupLaunch 2002-06-15 21:43:13+00:00 
[]
34 Meg replies megReplies 2002-06-15 22:44:22+00:00 
[]
35 Fixed validation again fixedValidationAgain 2002-06-16 12:19:15+00:00 
[]
36 My first XHTML mind bomb myFirstXhtmlMindBomb 2002-06-16 12:48:27+00:00 
[]
37 Jonathan on Mark jonathanOnMark 2002-06-16 13:12:42+00:00 
[]
38 University of Blogaria universityOfBlogaria 2002-06-16 13:22:48+00:00 
[]
39 Excited about XWT excitedAboutXwt 2002-06-16 15:10:54+00:00 
[]
40 Elm0 suggests libxml elm0Suggests 2002-06-16 22:44:15+00:00 
[]
41 Micah's alternative Yahoo micahsAlternativeYahoo 2002-06-17 01:13:27+00:00 
[]
42 Blog fixed blogFixed 2002-06-17 21:31:45+00:00 
[]
43 Styling <hr> stylingHR 2002-06-17 21:40:17+00:00 
[]
44 Day 6: Doctypes day6Doctype 2002-06-17 21:42:42+00:00 
[]
45 AllTheWeb claims allthewebClaims 2002-06-17 22:13:40+00:00 
[]
46 Open source economics openSourceEconomics 2002-06-17 22:29:28+00:00 
[]
47 Amazon with CSS amazonWithCSS 2002-06-17 22:57:31+00:00 
[]
48 Mark replies markReplies 2002-06-17 23:49:14+00:00 
[]
49 Minimal XML minimalXml 2002-06-18 15:29:36+00:00 
[
  {
    "4619": "xml"
  }
]
50 Knowledge Management knowledgeManagement 2002-06-18 15:38:59+00:00 
[]
51 Email interfaces emailInterfaces 2002-06-18 17:51:27+00:00 
[]
52 Suicidal chipmunk suicidalChipmunk 2002-06-18 18:11:45+00:00 
[]
53 PHP Documentor phpDocumentor 2002-06-18 18:59:36+00:00 
[]
54 Javascript select boxes javascriptSelectBoxes 2002-06-18 19:05:42+00:00 
[]
55 Language tag languageTag 2002-06-18 19:12:42+00:00 
[]
56 XHTML list xhtmlList 2002-06-18 19:22:52+00:00 
[]
57 Python and the space shuttle pythonSpaceShuttle 2002-06-18 22:24:19+00:00 
[
  {
    "3350": "python"
  }
]
58 Why software sucks whySoftwareSucks 2002-06-19 01:39:22+00:00 
[]
59 NPR link muppets nprLinkMuppets 2002-06-19 01:48:55+00:00 
[]
60 Andrei interview andreiInterview 2002-06-19 02:24:05+00:00 
[]
61 Advogato rant advogatoRant 2002-06-19 02:34:12+00:00 
[]
62 djc on Kuro5hin djcOnKuro5hin 2002-06-19 02:55:08+00:00 
[]
63 Free books freeBooks 2002-06-19 09:36:34+00:00 
[]
64 Day 8: page titles day8PageTitles 2002-06-19 09:45:49+00:00 
[]
65 Slashdot threads slashdotThreads 2002-06-19 13:45:06+00:00 
[]
66 SitePoint graphic design resources sitePointGraphicDesign 2002-06-19 14:26:44+00:00 
[]
67 XFML xfml 2002-06-20 01:04:49+00:00 
[
  {
    "4610": "xfml"
  },
  {
    "3130": "petervandijck"
  }
]
68 OOP and XP oopAndXP 2002-06-20 13:22:39+00:00 
[]
69 Apple rant appleRant 2002-06-20 14:02:48+00:00 
[]
70 Mozilla versions mozillaVersions 2002-06-20 15:50:05+00:00 
[]
71 RSS XML stylesheet rssXmlStylesheet 2002-06-20 16:54:52+00:00 
[]
72 Additional navigational links additionalNavigationalLinks 2002-06-20 18:50:17+00:00 
[]
73 Next-Prev implemented nextPrevImplemented 2002-06-20 20:38:13+00:00 
[]
74 Amazon's hairy feet amazonsHairyFeet 2002-06-20 21:21:12+00:00 
[]
75 Presenting your content first presentingYourContentFirst 2002-06-21 10:10:50+00:00 
[]
76 Dave's back davesBack 2002-06-22 16:21:55+00:00 
[]
77 NPR again nprAgain 2002-06-22 16:26:31+00:00 
[]
78 Building a semantic website semanticWebsite 2002-06-22 16:30:50+00:00 
[]
79 VillainSupply.com villainSupply 2002-06-22 20:33:58+00:00 
[]
80 Two things about Mozilla twoThingsAboutMozilla 2002-06-22 21:36:03+00:00 
[]
81 Comments added commentsAdded 2002-06-22 23:57:14+00:00 
[]
82 The Pickle Jar theory thePickleJarTheory 2002-06-24 16:29:42+00:00 
[]
83 Glastonbury Flash glastonburyFlash 2002-06-24 18:07:28+00:00 
[]
84 Mozilla page info mozillaPageInfo 2002-06-24 18:42:11+00:00 
[]
85 Skipping over navigation skippingOverNavigation 2002-06-24 18:54:14+00:00 
[]
86 Some Python advocacy somePythonAdvocacy 2002-06-24 19:21:25+00:00 
[
  {
    "3350": "python"
  }
]
87 PHP string tip phpTip 2002-06-24 20:12:19+00:00 
[]
88 Installing PHP and XSL on Windows installingPhpAndXslOnWindows 2002-06-24 23:16:54+00:00 
[]
89 An IA process anIaProcess 2002-06-24 23:39:42+00:00 
[]
90 Dot leaders in CSS dotLeadersInCss 2002-06-25 00:34:52+00:00 
[]
91 Writing IM Bots writingImBots 2002-06-25 10:00:06+00:00 
[]
92 Paul back soon paulBackSoon 2002-06-25 10:03:12+00:00 
[]
93 Kuro5hin on AudioGalaxy kuro5hinOnAudioGalaxy 2002-06-25 13:28:49+00:00 
[]
94 Using colour safely usingColourSafely 2002-06-25 15:00:16+00:00 
[]
95 SitePoint CSS guide sitepointCssGuide 2002-06-25 15:24:18+00:00 
[]
96 Oh ffs... ohFfs 2002-06-25 18:04:57+00:00 
[]
97 Semant-O-Matic semantOMatic 2002-06-25 23:57:57+00:00 
[]
98 The 5k the5k 2002-06-26 00:19:49+00:00 
[]
99 XML in Mozilla xmlInMozilla 2002-06-26 10:56:11+00:00 
[]
100 EuroPython starts euroPythonStarts 2002-06-26 11:11:06+00:00 
[
  {
    "3350": "python"
  }
]
101 Slashdot on XWT slashdotOnXwt 2002-06-26 11:40:41+00:00 
[]
102 PHP auto class inclusion phpAutoClassInclusion 2002-06-26 14:17:37+00:00 
[]
103 Use real links useRealLinks 2002-06-26 14:57:38+00:00 
[]
104 Cetus links cetusLinks 2002-06-26 15:30:39+00:00 
[]
105 Enterprise Application Architecture enterpriseApplicationArchitect 2002-06-26 23:12:45+00:00 
[]
106 Warchalking warchalking 2002-06-26 23:48:08+00:00 
[]
107 Gone to Glastonbury goneToGlastonbury 2002-06-26 23:53:00+00:00 
[]
108 Back from Glastonbury backFromGlastonbury 2002-07-01 23:33:10+00:00 
[]
109 Hixie goes open source hixieGoesOpenSource 2002-07-01 23:33:38+00:00 
[]
110 How Wolfenstein 5k works howWolfenstein5kWorks 2002-07-01 23:34:17+00:00 
[]
111 TrackBack trackBack 2002-07-01 23:53:06+00:00 
[
  {
    "4227": "trackback"
  },
  {
    "2732": "moveabletype"
  }
]
112 Arial and Helvetica arialAndHelvetica 2002-07-01 23:56:59+00:00 
[]
113 PHP form problem phpFormProblem 2002-07-02 18:58:47+00:00 
[]
114 More tips from Mark moreTipsFromMark 2002-07-02 19:43:03+00:00 
[]
115 I want this book iWantThisBook 2002-07-02 20:15:41+00:00 
[]
116 More on TrackBack moreOnTrackBack 2002-07-02 20:23:55+00:00 
[]
117 Guardian blogroll guardianBlogroll 2002-07-02 21:34:43+00:00 
[]
118 ThinkGeek soap thinkGeekSoap 2002-07-02 21:51:14+00:00 
[]
119 WGET tip wgetTip 2002-07-02 23:40:32+00:00 
[]
120 Banging headache bangingHeadache 2002-07-03 22:57:02+00:00 
[]
121 Message Catalog definition messageCatalog 2002-07-03 23:23:01+00:00 
[]
122 Digital Web magazine digitalWebMagazine 2002-07-03 23:28:15+00:00 
[]
123 Lego stuff legoStuff 2002-07-03 23:34:57+00:00 
[]
124 XML request object xmlRequestObject 2002-07-03 23:50:21+00:00 
[]
125 Alternative validator icons alternativeValidatorIcons 2002-07-03 23:57:44+00:00 
[]
126 New Mozilla rendering mode newMozillaRenderingMode 2002-07-04 10:25:11+00:00 
[]
127 Google interview googleInterview 2002-07-04 10:43:40+00:00 
[]
128 Spam proof email spamProofEmail 2002-07-04 15:58:01+00:00 
[]
129 Accessible tables accessibleTables 2002-07-04 18:24:50+00:00 
[]
130 Palladium palladium 2002-07-04 18:42:57+00:00 
[
  {
    "2639": "microsoft"
  },
  {
    "3734": "sethschoen"
  },
  {
    "3701": "security"
  },
  {
    "443": "boingboing"
  },
  {
    "3545": "robertcringely"
  },
  {
    "4127": "theregister"
  },
  {
    "3032": "palladium"
  }
]
131 K-Logging kLogging 2002-07-04 19:45:03+00:00 
[]
132 Blog update alerts blogUpdateAlerts 2002-07-04 19:57:53+00:00 
[]
133 Useful CSS links usefulCssLinks 2002-07-04 21:18:53+00:00 
[]
134 Home improvements homeImprovements 2002-07-04 23:55:24+00:00 
[]
135 Blog tracking solution blogTrackingSolution 2002-07-05 10:58:35+00:00 
[]
136 Funky popups funkyPopups 2002-07-05 11:31:53+00:00 
[]
137 More Python advocacy morePythonAdvocacy 2002-07-05 13:31:47+00:00 
[
  {
    "3350": "python"
  }
]
138 Rasmus Lerdorf's blog rasmusLerdorfsBlog 2002-07-05 13:45:35+00:00 
[]
139 Hixie BSc hixieBSc 2002-07-05 15:17:01+00:00 
[]
140 Opera and Macromedia operaAndMacromedia 2002-07-05 15:29:49+00:00 
[]
141 Stupid Danish newspapers stupidDanishNewspapers 2002-07-05 17:24:24+00:00 
[
  {
    "1007": "denmark"
  },
  {
    "953": "davewiner"
  },
  {
    "3993": "stupid"
  },
  {
    "2354": "linking"
  }
]
142 Final table tip finalTableTip 2002-07-05 17:29:31+00:00 
[]
143 <strong> and <em> strongAndEm 2002-07-05 17:42:09+00:00 
[]
144 Kevin Burton kevinBurton 2002-07-05 19:05:12+00:00 
[]
145 More on deep linking moreOnDeepLinking 2002-07-05 19:20:22+00:00 
[]
146 elgooG elgooG 2002-07-05 20:19:10+00:00 
[]
147 Janis Ian janisIan 2002-07-05 23:07:37+00:00 
[]
148 Better blogrolling betterBlogrolling 2002-07-06 00:02:05+00:00 
[]
149 More on blo.gs moreOnBloGs 2002-07-06 01:33:34+00:00 
[]
150 More blogroll fun moreBlogrollFun 2002-07-06 10:41:42+00:00 
[]
151 Interesting suggestion interestingSuggestion 2002-07-06 11:55:51+00:00 
[]
152 Mozilla sidebar added mozillaSidebarAdded 2002-07-06 12:32:34+00:00 
[]
153 The Two Way Web theTwoWayWeb 2002-07-06 22:14:01+00:00 
[
  {
    "953": "davewiner"
  },
  {
    "3833": "soap"
  },
  {
    "4621": "xmlrpc"
  }
]
154 Paper Scissors Stone paperScissorsStone 2002-07-07 11:57:12+00:00 
[]
155 Google OR googleOr 2002-07-07 12:11:32+00:00 
[]
156 Using web widgets wisely usingWebWidgetsWisely 2002-07-07 16:03:03+00:00 
[]
157 Ooh Muse.net oohMuseNet 2002-07-07 22:10:28+00:00 
[]
158 Zeldman on accessibility zeldmanOnAccessibility 2002-07-08 22:04:52+00:00 
[]
159 Language independant storage languageIndependantStorage 2002-07-08 22:09:27+00:00 
[]
160 Why workflow? whyWorkflow 2002-07-08 22:16:59+00:00 
[]
161 New bookmarklet newBookmarklet 2002-07-08 22:27:12+00:00 
[]
162 Sites bow to IE sitesBowToIE 2002-07-08 22:40:25+00:00 
[]
163 XML-RPC debugging xmlRpcDebugging 2002-07-09 09:07:35+00:00 
[
  {
    "3350": "python"
  },
  {
    "4621": "xmlrpc"
  }
]
164 Using SCP usingScp 2002-07-09 09:09:57+00:00 
[]
165 Webdocs.org webDocs 2002-07-09 09:18:46+00:00 
[]
166 Mozilla oddity mozillaOddity 2002-07-09 09:32:42+00:00 
[]
167 CherryPy cherryPy 2002-07-09 10:23:32+00:00 
[
  {
    "643": "cherrypy"
  },
  {
    "3350": "python"
  }
]
168 PHP XML Classes phpXmlClasses 2002-07-09 10:29:59+00:00 
[]
169 Open source CMS list openSourceCmsList 2002-07-09 10:46:53+00:00 
[]
170 Logoed logoed 2002-07-09 15:02:02+00:00 
[]
171 Accessible lists accessibleLists 2002-07-09 17:36:43+00:00 
[]
172 Rounded corners in CSS roundedCornersInCSS 2002-07-09 21:57:40+00:00 
[]
173 Alt all the way altAllTheWay 2002-07-10 10:12:49+00:00 
[]
174 xmlhack news wire xmlhackNewsWire 2002-07-10 12:50:15+00:00 
[
  {
    "4619": "xml"
  }
]
175 The semantic web explained semanticWebExplained 2002-07-10 15:49:55+00:00 
[]
176 CSS numbered code listings numberedCodeListing 2002-07-10 15:52:53+00:00 
[]
177 First Church of Pac-Man firstChurchOfPacman 2002-07-10 19:29:04+00:00 
[]
178 A million pounds down the drain aMillionPoundsDownTheDrain 2002-07-11 00:29:51+00:00 
[]
179 More Connected Earth moreConnectedEarth 2002-07-11 09:24:39+00:00 
[]
180 Image map accessibility imageMapAccessibility 2002-07-11 16:28:53+00:00 
[]
181 Lovely PNGs lovelyPNGs 2002-07-11 18:21:07+00:00 
[]
182 Bad faith my arse badFaithMyArse 2002-07-11 19:00:32+00:00 
[]
183 DVB-HTML dvbHtml 2002-07-12 11:34:36+00:00 
[]
184 Busy day busyDay 2002-07-12 19:33:40+00:00 
[]
185 Blog birthday blogBirthday 2002-07-12 19:45:50+00:00 
[]
186 Blogroll etiquette blogrollEtiquette 2002-07-12 19:52:48+00:00 
[]
187 Smarty at OSCON smartyAtOSCON 2002-07-12 20:06:52+00:00 
[]
188 Jonathan on longdesc jonathanOnLongdesc 2002-07-13 17:12:49+00:00 
[]
189 Maccaws maccaws 2002-07-14 02:52:55+00:00 
[]
190 Wiki fun wikiFun 2002-07-14 03:01:34+00:00 
[]
191 An excellent rant anExcellentRant 2002-07-14 03:07:39+00:00 
[]
192 Less is more lessIsMore 2002-07-14 03:13:33+00:00 
[]
193 Mozilla web author FAQ mozillaWebAuthorFAQ 2002-07-14 15:14:05+00:00 
[]
194 Maybe splash screens have a purpose maybeSplashScreensHaveAPurpose 2002-07-14 15:28:15+00:00 
[]
195 Which power puff girl is your blog? whichPowerPuffGirlIsYourBlog 2002-07-14 16:37:54+00:00 
[]
196 MySQL best practise mysqlBestPractise 2002-07-14 19:00:06+00:00 
[]
197 XML fun xmlFun 2002-07-15 02:18:18+00:00 
[]
198 Fifty two projects fiftyTwoProjects 2002-07-15 02:23:04+00:00 
[]
199 Python in PHP pythonInPHP 2002-07-15 02:34:22+00:00 
[
  {
    "3350": "python"
  },
  {
    "3158": "php"
  }
]
200 Accessible fonts accessibleFonts 2002-07-15 20:26:07+00:00 
[]
Copy and export data

Duration: 30.34ms