Skip to main content
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
44 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
  • 588
42 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
  • 521
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
  • 63.9k
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.5k
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
28 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
  • 63.9k
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
  • 63.9k
23 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
  • 63.9k
22 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
  • 63.9k
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
22 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
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

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
  • 30.2k
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
  • 63.9k
18 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
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
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
14 votes
Accepted

How to unnest and GROUP BY elements of a JSON array?

The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More: ...
Erwin Brandstetter's user avatar
14 votes
Accepted

jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"

As the name suggests, jsonb_array_elements() expects a JSON array to unnest. But, according to your error message, at least one row contains a jsonb value in address with a JSON object at the top ...
Erwin Brandstetter'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
  • 63.9k
13 votes
Accepted

Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns?

The advantage of JSON is versatility: you can add any keys without changing the table definition. And maybe convenience, if your application can read and write JSON directly. Separate columns beat a ...
Erwin Brandstetter's user avatar
13 votes
Accepted

Why is SQL Server spliting it's (JSON) response into multiple rows?

After further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks". Sql Server splits result of FOR JSON query into ~2KB chunk,...
joeldesante's user avatar
12 votes

MySQL: Return JSON from a standard SQL Query

For an array of JSON objects (one object per row in query), you can do this: SELECT JSON_ARRAYAGG(JSON_OBJECT("fieldA", fieldA, "fieldB", fieldB)) FROM table; It would result in a single JSON ...
Eneko Alonso's user avatar

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