Skip to main content

Questions tagged [json]

JavaScript Object Notation (JSON) is an open, human and machine-readable standard that facilitates data interchange, and along with XML is the main format for data interchange used on the modern web.

Filter by
Sorted by
Tagged with
140 votes
8 answers
453k views

How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string ('foo, ...
Christoph's user avatar
  • 1,593
72 votes
9 answers
127k views

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
AliBZ's user avatar
  • 1,807
59 votes
7 answers
174k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
Yanick Rochon's user avatar
56 votes
4 answers
114k views

Is it possible to store and query JSON in SQLite?

I need to store JSON objects in a SQLite database, and then do complex queries on it. I did a table like this: +--------------------------------------+ |document | property | string | number| +----...
tuxlu's user avatar
  • 561
49 votes
2 answers
81k views

Replace multiple columns with single JSON column

I am running PostgreSQL 9.3.4. I have a table with 3 columns: id name addr 1 n1 ad1 2 n2 ad2 I need to move the data to a new table with a JSON column like: id data 1 {"name": "n1&...
AliBZ's user avatar
  • 1,807
43 votes
2 answers
77k views

postgresql: how to define a JSONB column with default value

I am unable to find in the documentation how to create a JSONB column in PostgreSQL that has a DEFAULT value of an empty json document. How the above can be stated in the CREATE TABLE definition ?
nskalis's user avatar
  • 1,621
34 votes
3 answers
42k views

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

Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes? Or should this question be ...
Hvisage's user avatar
  • 473
34 votes
2 answers
80k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
Kokizzu's user avatar
  • 1,373
34 votes
1 answer
77k views

Query JSON array of obejcts against multiple values

I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups. Given this example table: CREATE TABLE grp(d jsonb NOT NULL)...
BartZ's user avatar
  • 443
31 votes
4 answers
47k views

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to ...
Maxim Yefremov's user avatar
29 votes
2 answers
47k views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
Golo Roden's user avatar
26 votes
1 answer
78k views

See if an JSON array in MySQL contains an object whose key holds a specific date

i'm trying to find out if there is a row which contains a specific date inside a JSON array Let's say my data looks like this: Table applications: id | application_id | data # Rows 1 | 1 | [{"...
Klemen's user avatar
  • 591
25 votes
6 answers
127k views

MySQL: Return JSON from a standard SQL Query

I have read about JSON objects and the JSON object type. I only want to do a select and it return JSON. I do not necessarily want to store a JSON object. Serialization per se it not my question. ...
johnny's user avatar
  • 612
25 votes
2 answers
83k views

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count from ...
Rabi C Shah's user avatar
22 votes
1 answer
24k views

Creating a UNIQUE constraint from a JSON object

Lets take some example table peoples , that got only 2 fields: id and data(json). SELECT data FROM peoples ; {"name": "Adam","pos":"DBA","age":22 } {"name": "Alice","pos":"Security","age":33 } {"name"...
Chenko47's user avatar
  • 351
21 votes
3 answers
29k views

How can I use a full-text search on a jsonb column with Postgres?

So i have a jsonb column that has entries like this: https://pastebin.com/LxJ8rKk4 Is there any way to implement a full-text search on the entire jsonb column?
choco's user avatar
  • 211
18 votes
1 answer
27k views

Loop through key/value pairs of a jsonb object in postgresql function

I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object. create or replace function myFunction (input jsonb) returns jsonb as $$ BEGIN // ...
JaneL's user avatar
  • 183
18 votes
1 answer
52k views

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

I want to verify that an json[] column the_array is not empty. For a normal array, I could check on: the_array != '{}' However, that does not work for a json[], as demonstrated below: select '{}'::...
Jarius Hebzo's user avatar
18 votes
3 answers
62k views

Update a json element in json datatype

I can't figure how can I update an element in a PostgreSQL 9.3 datatype. My example: CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender integer ...
Sebastien Deschamps's user avatar
17 votes
2 answers
39k views

Does MariaDB support native JSON column data type?

I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version? CREATE TABLE example (names JSON); As far ...
Omranic's user avatar
  • 271
16 votes
1 answer
40k views

Postgres query to return JSON object keys as array

Is it possible to return a JSON object keys as an array of values in PostgreSQL? In JavaScript, this would simply be Object.keys(obj), which returns an array of strings. For example, if I have a ...
Yanick Rochon's user avatar
16 votes
1 answer
33k views

Postgres function to merge two json objects with overlapping keys into one object

I've got the following JSON object: { "a" : { "0" : 2, "1" : 4, "3" : 6, }, "b" : { "2" : 8, "1" : 10, /*note this key exists in "a" too*/ "...
Max Hudson's user avatar
15 votes
2 answers
34k views

Creating an array from multiple columns without NULL elements

I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below: CREATE TEMPORARY TABLE foo AS SELECT * FROM ( VALUES (1,'Router','...
Sathariel's user avatar
  • 253
12 votes
3 answers
33k views

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

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
Bax's user avatar
  • 303
12 votes
2 answers
41k views

Find rows containing a key in a JSONB array of records

I'm trying to query for a key present in an array of objects. This structure: column jdata {"name": "Somedata", "array": [ {"name":"bla1", "attr&...
Kobus's user avatar
  • 151
12 votes
1 answer
43k views

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
Trasplazio Garzuglio's user avatar
12 votes
2 answers
17k views

PostgreSql JSONB SELECT against multiple values

I have a very simple JSON table which I populate with some sample data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}'); INSERT INTO ...
jgm's user avatar
  • 305
11 votes
5 answers
35k views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
antsyawn's user avatar
  • 255
11 votes
1 answer
24k views

PostgreSQL filtering on array length inside JSON

I have a table table with a JSONB field data, which contains a variable-length array, e.g. {"label": "xyz", "items": [ ... ]} I created an index on the length of the "items" element: CREATE INDEX ...
shadowtalker's user avatar
11 votes
5 answers
26k views

Import in postgres json data in a csv file

I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type. One line of my csv file contains something like 1,{"a":"b"} Suppose the ...
marcosh's user avatar
  • 243
11 votes
3 answers
18k views

How to insert values into a table with default values from a select query in PostgreSQL?

Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null? In my case, the SELECT statement is selecting from JSON. In ...
Jay Cummins's user avatar
10 votes
1 answer
13k views

Extract JSON array of numbers from JSON array of objects

I have a table containing a json array of objects similar to this: id | record ____________________ name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...] I would like to get a table ...
Vakarelov's user avatar
  • 103
10 votes
1 answer
15k views

Is the size limit of a json field before or after compression in Postgres?

The size limit of a json field is 1GB (source: this StackOverflow answer): json is the same as a text datatype but with JSON validation. The text datatype's maximum size is 1GB. I ran some ...
Vegard Stikbakke's user avatar
10 votes
2 answers
63k views

"ERROR:malformed array literal" when using json_to_record with a JSON array element in Postgres 9.4

This illustrates the issue nicely: When column b is of type text, and not an array, the following works: select * from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') as x(a int, b ...
Taytay's user avatar
  • 325
10 votes
2 answers
13k views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
Antonio's user avatar
  • 201
9 votes
2 answers
9k views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
Christoph's user avatar
  • 1,593
9 votes
2 answers
21k views

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

Using: Postgres 14.2. Objective: To get a list of all distinct countries in my table. The column address is a JSONB column type and contains an array structured like: { "address":[ {...
bob_cobb's user avatar
  • 193
9 votes
1 answer
2k views

Do long names for jsonb keys use more storage?

Consider the example: Two tables foo and bar, each with a jsonb column. For foo, there are a million rows where the value of the jsonb is [{"a":123}]. For bar, there are a million rows where the ...
davidtgq's user avatar
  • 739
9 votes
2 answers
10k views

Postgresql json column to view

I have a table that looks like this id | json_column ---+------------ 1 | {"text1":"a", "text2":"b", "text3":"c", ....} 2 | {"text1":"b", "text2":"c", ....} and I want it to be like this text1 |...
CherrySkizz's user avatar
9 votes
2 answers
15k views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
thomaskeefe's user avatar
9 votes
1 answer
6k views

Is there any sense in using Postgres array of JSONB values instead of only JSONB?

Is there any reason to use array of JSONB values instead of just raw JSONB? My use case is that I need to store custom forms definition in list of JSON objects. And my question is whether it would be ...
Hauleth's user avatar
  • 205
8 votes
2 answers
12k views

Update all values for given key nested in JSON array of objects

I have a jsonb column data in a Postgres table my_table. It contains the following data: [ {"id":"1","status":"test status1","updatedAt":"1571145003"}, {"id":"2","status":"test status2","...
kishram's user avatar
  • 83
8 votes
1 answer
6k views

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

I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
joeldesante's user avatar
8 votes
2 answers
9k views

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

For example, say I have a table with a medium number of rows (~100,000 or so) that has a jsonb column with the following example data in one of the rows: {"name":"Bob", "...
ben_makes_stuff's user avatar
8 votes
2 answers
33k views

Aggregate objects into a json array (subquery issues)

I'm sorry for the vague title, but I simply don't know the right words to describe this. I have this query that transforms a bunch of columns into an object that works just fine: SELECT row_to_json(t) ...
ervazu's user avatar
  • 195
8 votes
1 answer
24k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
spa's user avatar
  • 213
8 votes
2 answers
12k views

Primary key within JSON data in Postgresql

I have a table t, with a column called json, of type JSON. Within the JSON there is a natural key: > SELECT json->'id' AS id FROM t LIMIT 1; id -----------------------...
davetapley's user avatar
8 votes
1 answer
12k views

Must numeric JSON keys be quoted?

I've noted that key-value values may be unquoted when numeric in TSQL JSON strings, but it seems the key component must always be quoted. select 1, isjson(''), 'empty string' union select 2, isjson('{...
youcantryreachingme's user avatar
8 votes
3 answers
6k views

What can I do with the new json field?

PostgreSQL 9.2 introduced the json field type. Why and when should I use it? What benefits does it have over a text field? I thought there were new query options available, however I haven't seen ...
Jonathan Egerton's user avatar
8 votes
1 answer
8k views

Can jsonb do fast search by *value* or even a partial value? Other types?

I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice. Example data: id: 1, name: "Joe Doe", preferences: { color: "red" , ...
Alex Takitani's user avatar

1
2 3 4 5
14