135
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() ...
81
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.
...
78
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 ...
65
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 ...
52
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.
47
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
66.4k12 gold badges128 silver badges166 bronze badges
27
This would be more efficient:
With jsonb and jsonb_array_elements_text() in pg 9.4+
EXPLAIN
SELECT p.id AS p_id, p.data
, c.id AS c_id, c.data
FROM test p
LEFT JOIN LATERAL jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE
LEFT JOIN test c ON c.id = pc.child::int;
db<>fiddle here
About jsonb_array_elements_text():
How to turn ...
27
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.
27
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\&...
26
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 ...
25
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
66.4k12 gold badges128 silver badges166 bronze badges
25
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;
25
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 ...
23
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;
...
23
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
22
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 ...
21
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 ...
21
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.
21
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 ...
18
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
140k17 gold badges331 silver badges475 bronze badges
16
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;
15
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.
...
15
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,
...
15
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 ...
15
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 ...
14
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 ...
14
The default json->text coercion outputs with a double quote (") because coercing from text to a json string requires you to double-quote your input. To get rid of the double-quotes, use TRIM
SELECT x, trim('"' FROM x::text)
FROM json_array_elements('["one", "two"]'::json) AS t(x);
x | btrim
-------+-------
"one" | one
"two" | two
(2 rows)
...
13
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"}
]
}...
13
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 ...
12
Please find below the only answer that outputs actually valid JSON (i.e. array of objects).
\t
\a
\o data.json
select json_agg(t) FROM (SELECT * from table) t;
(source)
Only top voted, non community-wiki answers of a minimum length are eligible