86
Postgres 9.4 or newer
Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!
json_array_elements_text(json)
jsonb_array_elements_text(jsonb)
To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() ...
47
There is a better option with json_build_object() in Postgres 9.4+:
SELECT id, json_build_object('name', name, 'addr', addr) AS data
FROM myt;
But there is also a simpler and faster way with row_to_json() in Postgres 9.3:
SELECT id, row_to_json((SELECT d FROM (SELECT name, addr) d)) AS data
FROM myt;
db<>fiddle here
Old SQL Fiddle on Postgres 9.6.
...
45
Try here for a basic intro to PostgreSQL and JSON.
Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.
Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format
{'id':1,'name':'David'}
{'id':2,'name':'James'}
...
I didn't have a running instance of ...
44
Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression:
SELECT a.id, a.name
, json_agg((b.col1, b.col2, b.col3)) AS item
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
That's short for the more ...
32
SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data .
Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well.
21
This would be more efficient:
With json and json_array_elements() in pg 9.3
SELECT p.id AS p_id, p.data AS p_data
, c.id AS c_id, c.data AS c_data
FROM test p
LEFT JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE
LEFT JOIN test c ON c.id = pc.child::text::int;
Use the -> operator instead of ->> in the reference to ...
16
Starting with 9.6 you can simply use - to remove a key from a JSONB:
SELECT a.id, a.name, jsonb_agg(to_jsonb(b) - 'item_id') as "item"
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
to_jsonb(b) will convert the whole row and - 'item_id' will then remove the key with the name item_id the result of that is then aggregated.
answered Mar 14 '18 at 14:53
a_horse_with_no_name
45.6k9 gold badges89 silver badges122 bronze badges
16
PG 9.4+
The accepted answer is definitely what you need, but for the sake of simplicity here is a helper I use for this:
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(
p_input jsonb
) RETURNS TEXT[] AS $BODY$
DECLARE v_output text[];
BEGIN
SELECT array_agg(ary)::text[]
INTO v_output
FROM jsonb_array_elements_text(p_input) AS ary;
...
16
That's the same as with any other default value:
create table amsterdam
(
id integer primary key,
payload jsonb not null default '{}'::jsonb
);
answered May 17 '18 at 9:02
a_horse_with_no_name
45.6k9 gold badges89 silver badges122 bronze badges
15
It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup.
Assumptions
Assuming this table definition:
CREATE TABLE tbl (tbl_id int, usr jsonb);
"user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead.
Query
The ...
14
First and foremost: I agree with both the comments of @a_horse_with_no_name and @dezso: you should normalize your data. JSON is not for that.
However, if some reason I cannot fathom really makes this an advantage, it is possible:
Create an expression based UNIQUE INDEX:
CREATE UNIQUE INDEX people_data_pos_idx ON peoples( (data->>'pos') ) ;
If, at ...
13
Try this instead:
CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int));
The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se.
...
12
There is a way: combine the containment operator @> with the ANY construct:
SELECT d
FROM grp
WHERE d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);
Or:
...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);
It's essential to cast the array to jsonb[] explicitly. And note that ...
11
If you're using psql then there is no reason to use \COPY at all.
\t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;
This is the same method we use to get png/jpgs/tifs out of the database with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.
11
Assuming a JSON array in Postgres 9.4 jsonb column, this would do the job:
SELECT DISTINCT ON (doc->'s') doc
FROM (
SELECT '[
{
"id":1,
"s":"foo"
},
{
"id":2,
"s":"bar"
},
{
"id":3,
"s":"foo"
}]'::jsonb AS j
) t
, jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn)
ORDER BY doc->'...
11
Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it:
SELECT id, (ARRAY(SELECT json_object_keys(obj)) AS keys
FROM tbl_items;
Or use jsonb_object_keys() for jsonb.
This returns an array of keys per row (not ...
11
This cannot work because it's trying to cast a jsonb value to integer.
select data->'name' as name from persons where cast(data->'age' as int) > 25
This would actually work:
SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;
Or shorter:
SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;
...
10
jsonb in Postgres 9.4 or later
You will be interested in the jsonb data type in Postgres 9.4. 'b' is for 'binary'. Among other things, there is an equality operator = for jsonb. Most people will want switch to jsonb.
Depesz blog about jsonb.
json
There is currently no = operator defined for the data type json, because there is no well defined method to ...
10
I was able to emulate json_object_agg using string_agg (which is available in 9.3).
Your example would be:
select user, ('{' || string_agg('"' || offer || '": ' || clicks, ',') || '}')::json as activity
from click_activity
group by user
10
One solution as provided by Morgan Tucker - @morgo is to use json_contains like so:
select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}')
For now the answer is OK, but i believe it can have some performance issues and feels a bit hackish to me (see the next query) - but I will deal with those when i get there :)
If I would need ...
10
PostgreSQL 10+
PostgreSQL 10 introduces Full Text Search on JSONB
CREATE INDEX ON table
USING gin ( to_tsvector('english',jsondata) );
The new FTS indexing on JSON works with phrase search and skips over both the JSON-markup and keys.
10
Took a bit of figuring out (more used to PostgreSQL where things are much easier!), but if you consult the fine manual here, under 12.16.2 Functions That Create JSON Values, there's the JSON_ARRAY function, but it's not much use really - at least in this case!
To answer the question "select and it return JSON", there are two ways of doing this, both rather ...
10
If you are strictly confident that your nested arrays will never go deeper than N levels, you can completely unwrap the array-of-arrays with N uses of APPLY. If you need to handle for arbitrary nesting levels, you can unwrap the array-of-arrays recursively using something like the following, which will produce output similar to the following
|----|-------|--...
9
Alright, so you seem to want to create a graph from a join tree. That's a pretty natural thing to want to do.
It's not as easy as it should be in Pg, mainly because of the lack of support for column-aliases in the record pseudo-type.
Here's what I came up with:
SELECT row_to_json(r, true)
FROM (
SELECT
d.id,
d.name,
...
9
If you have MySQL 5.7.13 or later, you may use JSON_UNQUOTE() instead of JSON_EXTRACT() or ->> instead of ->. Example:
SELECT field->>"$.foo.barr" FROM table;
9
Yes key length matters
The bigger the key, the more space to store it. JSONB doesn't do anything special to keys.
Test Cast
Sample Data
# CREATE TABLE foo AS SELECT '{"f":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000
# CREATE TABLE bar AS SELECT '{"very_long_key_not_premature_optimization_at_all":true}'::jsonb FROM generate_series(1,1e6);
...
8
This question was asked on the PostgreSQL mailing lists and I came up with this hackish way of converting JSON text to PostgreSQL text type via the JSON field extraction operator:
CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||$1||']')::json->>0 $$;
db=# select json_text(json_array_elements('["hello",1.3,"\...
8
If you're using PostgreSQL 9.5 (at least), you can use the concatenation operator (||) on jsonb types (and you can convert json to jsonb if necessary first).
For example:
WITH test(data) AS (
VALUES ('{
"a" : {
"0" : 2,
"1" : 4,
"3" : 6
},
"b" : {
"2" : 8,
"1" : 10,
...
8
If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle:
select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds"
from container
1.
SELECT ...
8
Converting a row to json
It doesn't sound to me like you want to aggregate JSON. You state you want the equivalent of row_to_json, if so then I suggest checking out the much simpler JSON_OBJECT
SELECT JSON_OBJECT(
'name_field', name_field,
'address_field', address_field,
'contact_age', contact_age
)
FROM contact;
Aggregating JSON
As a side note, if ...
Only top voted, non community-wiki answers of a minimum length are eligible
