We don't want any of that included in our final output and will deal There are two things to note regarding this output.ĭashed line header at the top and weird spacing between results marked by the developer_id ) select json_agg ( t ) from t Įither way, we get a JSON result that looks something like the following: - [, + channel_id join developers on developers. username from posts join channels on channels. developer_id ) t json_agg with CTE with t as ( select title, body, published_at, '' || slug as, channels. json_agg with subquery select json_agg ( t ) from ( select title, body, published_at, '' || slug as, channels. Use the CTE version throughout the rest of the post. Subquery or a CTE (common table expression). To prepare our previous statement for json_agg we need to wrap it in either a Postgres has a json_agg function which will do most of the heavy lifting here. We want to transition from a tabular result to a JSON one. The results come back that way, even something as simple as select 1 is Transitioning To JSONĭata in a Postgres database is always in a tabular form. With the shape and content of our dump settled, let's move on to the JSON step. select title, body, published_at, '' || slug as, channels.
Let's push this example a bit further by joining in theĬhannel names for each post as well as attributing each post to the developer Great! We took a big step in the right direction by pulling out values we want com / posts / 97 e26f5f68 - + - title | Variable Hoisting in JavaScript body | I while back I wrote about + - title | Three ways to compile Elixir. select title, body, published_at, '' || slug as from posts - + - title | Start rails server in production mode body | `rails s -e production`. There is no field, but we can easily construct one from
Three - title, body, and published_at - map directly to columns on Title, body, published_at, and a for each post. There are a lot of columns in this table. The posts table can be described as follows: \ d posts Table "public.posts" Column | Type | Modifiers -+-+- id | integer | not null default nextval ( 'posts_id_seq' :: regclass ) title | character varying | not null body | text | not null inserted_at | timestamp with time zone | not null default now () updated_at | timestamp with time zone | not null default now () channel_id | integer | not null slug | character varying ( 255 ) | not null likes | integer | not null default 1 max_likes | integer | not null default 1 published_at | timestamp with time zone | not null default now () developer_id | integer | tweeted_at | timestamp with time zone | To make this a bit more concrete, let's consider how we would create a dataĭump of the Today I Learned posts from our tilex app. Map directly where as others will require computing a value based on aĬombination of rows or some known constant.
Need and consider how cleanly that maps to your data model. We are creating a JSON dump of our data because someone needs access to ourĭata. We will need to come up with the shape and content of theĭata as discussed above, turn the result table into JSON using json_agg,Īdjust some PSQL settings related to the output formatting, and, lastly, To dump a subset of our data from Postgres as JSON, we'll need to do aĬouple things. JSON parser with which they can read that data into a program meant to consume That any developer regardless of their development stack will have access to a We are not only ensuring that it is relatively human-readable, we also know Second, JSON is a clean and friendly export format.
#Postgres json query serial
For instance, we may not want toĮxpose serial ids that make up the primary key of our data. Likewise, weĬan exclude sensitive and irrelevant data. Why a JSON dump?Ī customized JSON dump of our data is appealing for two reasons.įirst, we can craft the shape of the data being dumped.
#Postgres json query how to
This post we'll explore how to quickly create a JSON dump of some of our data. That we can easily pass along to others? A JSON dump would fit that bill. What if we want to export some subset of our data in a general purpose format Perhaps we even occasionally create a dump of theĭatabases' schema and data using pg_dump. We build apps that query against our database.
There are lots of ways that we consume the data stored in our PostgreSQLĭatabases. PostgreSQL Create Quick JSON Data Dumps From PostgreSQL