Hot answers tagged

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 ...
user avatar
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.) ...
user avatar
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 ...
user avatar
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 );
user avatar
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 ...
user avatar
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
user avatar
  • 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 ...
user avatar
  • 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":&...
user avatar
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 ...
user avatar
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;
user avatar
  • 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; ...
user avatar
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 (...
user avatar
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 ...
user avatar
  • 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' ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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: ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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;
user avatar
  • 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 ...
user avatar
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 ( ...
user avatar
  • 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-...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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)
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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$ ...
user avatar
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 ...
user avatar
  • 231

Only top scored, non community-wiki answers of a minimum length are eligible