121
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() ...
71
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 ...
71
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.
...
61
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 ...
47
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.
42
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
61.8k12 gold badges116 silver badges154 bronze badges
24
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 ...
24
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 ...
23
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 ...
22
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
61.8k12 gold badges116 silver badges154 bronze badges
22
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;
21
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 ...
20
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[]
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT array_agg(ary)::text[] FROM jsonb_array_elements_text(p_input) AS ary;
$function$;
Then just do:
SELECT ...
20
This does not 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;
...
20
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.
20
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 ...
19
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 ...
18
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.
17
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
15
If you are altering an already existing table, then the syntax is as follows:
alter table TABLE add column COLUMN jsonb not null default '{}'::jsonb;
14
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.
...
13
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,
...
13
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 ...
12
You can actually do it without group by, using subqueries
SELECT
a.id, a.name,
(
SELECT json_agg(item)
FROM (
SELECT b.c1 AS x, b.c2 AS y
FROM b WHERE b.item_id = a.id
) item
) AS items
FROM a;
returns
{
id: 1,
name: "thing one",
items:[
{ x: "child1", y: "child1 col2"},
{ x: "child2", y: "child2 col2"}
]
}...
12
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 for ...
answered Jan 25 '16 at 15:02
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
12
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 ...
11
jsonb in Postgres 9.4 or later
Consider the jsonb data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=) for jsonb. Most people will want to switch.
Depesz blog about jsonb.
json
There is no = operator defined for the data type json, because there is no well defined method to ...
11
Assuming a JSON array in a 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-&...
answered May 27 '15 at 16:46
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
11
Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).
One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this ...
11
I would suggest not using a JSON array, and instead using the native SQL array syntax which is likely much faster and more efficiently stored. It's also stronger typed. The JSON array is "possibly-heterogeneously-typed" per the docs.
I also wouldn't do this routinely. I would alter the table's schema to have an ARRAY (preferably SQL) on the table itself to ...
Only top voted, non community-wiki answers of a minimum length are eligible
