Hot answers tagged

54 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 ...
ThePhysicist's user avatar
41 votes

Unquoting JSON strings; print JSON strings without quotes

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json); Result: col --- one two
Slava V's user avatar
  • 511
40 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;
AXM's user avatar
  • 548
39 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 ...
Evan Carroll's user avatar
  • 62.7k
39 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 ...
joanolo's user avatar
  • 13.4k
36 votes
Accepted

Query JSON array of obejcts against multiple values

Postgres 12 or later You can use an SQL/JSON path expression with the @? operator: SELECT d FROM grp WHERE d @? '$.customers[*].id ? (@ == "1" || @ == "5")'; Note the double ...
Erwin Brandstetter's user avatar
29 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: ...
Erwin Brandstetter's user avatar
29 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 (...
Erwin Brandstetter's user avatar
28 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)
Gunar Gessner's user avatar
26 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' AS ...
Erwin Brandstetter's user avatar
25 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 ...
Evan Carroll's user avatar
  • 62.7k
24 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 ...
Evan Carroll's user avatar
  • 62.7k
23 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 ...
Klemen's user avatar
  • 591
22 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 my_table ADD COLUMN my_column JSONB NOT NULL DEFAULT '{}'::jsonb;
enomad's user avatar
  • 337
21 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-...
Evan Carroll's user avatar
  • 62.7k
21 votes

What is the fastest way to verify that a json[] array is not empty?

This is not returning a JSON Array,. SELECT '{}'::json[] The type json[] is not a "JSON Array", it's a SQL Array of type JSON. You should never be using this kind of structure. It's almost certainly ...
Evan Carroll's user avatar
  • 62.7k
21 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$ ...
Erwin Brandstetter's user avatar
20 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 ( ...
Bruno's user avatar
  • 1,535
19 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 ...
Erwin Brandstetter's user avatar
19 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 ...
Evan Carroll's user avatar
  • 62.7k
18 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 ...
Vérace's user avatar
  • 29.7k
17 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 ...
András Váczi's user avatar
17 votes
Accepted

Find rows containing a key in a JSONB array of records

Postgres 12 or later: with SQL/JSON path expression SELECT * FROM tbl WHERE jdata->'array' @? '$ ? (exists (@."attr"))'; You can look for keys or values, only in the outer nesting ...
Erwin Brandstetter's user avatar
15 votes

How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?

I can't comment on Alex Markov's answer because I don't have enough reputation. In my case using JSON_UNQUOTE instead of JSON_EXTRACT doesn't work, but I can get the result by wrapping the second ...
Grumoll's user avatar
  • 251
14 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 ...
redben's user avatar
  • 241
14 votes

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements_text('["one", "two"]'::json) of if using jsonb instead: SELECT jsonb_array_elements_text('["one", "two"]'::jsonb)
Daniel Canales's user avatar
13 votes
Accepted

Do long names for jsonb keys use more storage?

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 ...
Evan Carroll's user avatar
  • 62.7k

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