92
votes
Accepted
Select columns inside json_agg
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 ...
- 152k
88
votes
Postgres multiple columns to json
Simplest with the operator jsonb - text → jsonb to remove a single key in Postgres 9.5 or later - after converting the whole row with to_jsonb(). (Cast the result to json if you don't want jsonb.)
...
- 152k
67
votes
Accepted
Export Postgres table as json
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 ...
- 27.2k
58
votes
Accepted
postgresql: how to define a JSONB column with default value
That's the same as with any other default value:
create table amsterdam
(
id integer primary key,
payload jsonb not null default '{}'::jsonb
);
- 71.2k
52
votes
Is it possible to store and query JSON in SQLite?
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 ...
- 601
34
votes
Unquoting JSON strings; print JSON strings without quotes
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
- 441
31
votes
Export Postgres table as json
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 ...
- 56.2k
31
votes
Accepted
PostgreSQL JSON query array against multiple values
There is a way: combine the containment operator @> with the ANY construct:
SELECT d
FROM grp
WHERE d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":&...
- 152k
29
votes
Accepted
PostgreSQL joining using JSONB
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 ...
- 152k
29
votes
Accepted
How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?
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;
- 438
28
votes
Select columns inside json_agg
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;
...
- 71.2k
28
votes
How to get particular object from jsonb array in PostgreSQL?
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 (...
- 152k
26
votes
Accepted
Creating a UNIQUE constraint from a JSON object
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 ...
- 12.5k
24
votes
Querying JSONB in PostgreSQL
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' ...
- 152k
23
votes
How can I use a full-text search on a jsonb column with Postgres?
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 ...
- 56.2k
22
votes
How to turn JSON array into Postgres array?
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 ...
- 379
22
votes
Accepted
Postgres query to return JSON object keys as array
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:
...
- 152k
22
votes
See if an JSON array in MySQL contains an object whose key holds a specific date
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 ...
- 551
22
votes
MySQL: Return JSON from a standard SQL Query
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
...
- 56.2k
19
votes
postgresql: how to define a JSONB column with default value
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;
- 307
18
votes
Accepted
Build JSON object from one-to-many relationship data in a single query?
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 ...
- 152k
17
votes
Postgres function to merge two json objects with overlapping keys into one object
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 (
...
- 1,457
16
votes
Accepted
Creating an array from multiple columns without NULL elements
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-...
- 56.2k
16
votes
Accepted
MySQL: Return JSON from a standard SQL Query
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 ...
- 27.2k
15
votes
Accepted
Unquoting JSON strings; print JSON strings without quotes
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
...
- 56.2k
14
votes
Export Postgres table as json
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)
- 249
14
votes
Accepted
Is it possible to select distinct values in a json document using PostgreSQL?
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,
...
- 152k
14
votes
Accepted
PostgreSQL filtering on array length inside JSON
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 ...
- 28.9k
14
votes
Accepted
Loop through key/value pairs of a jsonb object in postgresql function
Use jsonb_each(jsonb) or jsonb_each_text(jsonb) in a FOR loop like:
CREATE OR REPLACE FUNCTION my_function(input jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS -- language declaration required
$func$
...
- 152k
13
votes
Select columns inside json_agg
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
...
- 231
Only top scored, non community-wiki answers of a minimum length are eligible