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
82
votes
5answers
208k 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, ...
40
votes
6answers
58k 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
39
votes
3answers
70k 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| +----...
29
votes
6answers
69k 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 ...
27
votes
2answers
37k views

Postgres multiple columns to json

I am running postgresql 9.3.4. I have a table with 3 fields: id name addr --- ---- ---- 1 n1 ad1 2 n2 ad2 ... I need to move the data to a new table with fields like: id data --- ...
19
votes
1answer
32k views

PostgreSQL JSON query array against multiple values

I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups. Given this example table: CREATE TABLE grp(d JSONB NOT NULL); INSERT INTO ...
19
votes
1answer
47k 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 | [{"...
18
votes
2answers
35k 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":...
17
votes
1answer
50k 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 ...
16
votes
1answer
20k 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). ...
15
votes
2answers
18k 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 ?
15
votes
3answers
43k 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 ...
14
votes
2answers
9k 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?
13
votes
2answers
26k 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 ...
12
votes
4answers
61k 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. ...
12
votes
2answers
12k 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 ...
11
votes
2answers
14k views

MySQL 5.7 JSON_EXTRACT: how to remove string quotes?

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 ...
9
votes
1answer
9k 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"...
9
votes
2answers
9k 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 ...
9
votes
2answers
8k 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"') ,('[]', '"...
9
votes
2answers
38k 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 ...
8
votes
2answers
17k 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','...
8
votes
1answer
12k 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*/ "...
8
votes
1answer
15k 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 ...
8
votes
3answers
11k 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 ...
8
votes
3answers
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 ...
8
votes
1answer
12k views

Postgresql: Aggregate objects into an (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(...
7
votes
2answers
7k 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 ...
7
votes
1answer
1k 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 ...
7
votes
3answers
11k 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 ...
7
votes
1answer
6k views

Fetch nested results as JSON PostgreSQL 9.2

Consider the following tables Device -------- id name type -------- components -------- id INT type VARCHAR -------- Manufacturers ------------- id INT name VARCHAR country VARCHAR ------------- ...
7
votes
1answer
8k 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 ...
7
votes
1answer
1k views

Inconsistent statistics on jsonb column with btree index

I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly ...
7
votes
3answers
11k 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 ...
7
votes
2answers
7k 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 ...
6
votes
2answers
2k views

Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]

Frequently in Javascript you'll have something like [ 7,2, [6,7], 2,10 ] How would you query that structure with OPENJSON I would like this, 0, 7 1, 2 2, 6, 2, 7, 3, 2 4, 10 I'm having a hard time ...
6
votes
5answers
19k 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) ...
6
votes
1answer
17k 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 ...
6
votes
1answer
18k 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: ...
6
votes
1answer
2k 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('{...
6
votes
2answers
4k views

Get all rows from table where JSON column contains a certain value

I'm struggling to get data from a JSON column in my PostgreSQL database. In our users table, we have a known_ips column, which is a JSON column, holding a flat array of IP addresses known for the ...
6
votes
2answers
5k 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 |...
6
votes
1answer
4k 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" , ...
6
votes
1answer
2k views

Postgres 9.4+: JSONB instead of EAV

I'm figuring out a way to structure a new PostgreSQL database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seems like a flexible ...
6
votes
1answer
17k views

PostgreSQL update JSONB without jsonb_set

Would there be any drawbacks to using a normal update statement to update a json(b) column like so: update "events" set "properties" = '{"type":"graph"}' Instead of using the jsonb_set function ...
6
votes
1answer
1k views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
6
votes
1answer
3k views

PostgreSQL, finding elements by value in numeric JSON arrays

I have a table defined as: create table dummy (jdata jsonb); I inserted the following two rows: insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ('[1,2,3,4]')...
6
votes
1answer
1k views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
5
votes
3answers
5k views

Passing variable as parameter to json_query function

I am getting error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal ...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it. ...
5
votes
2answers
4k views

Can SQL Server 2016 extract node names from JSON?

I want to extract a complete list of the node names and their paths from any arbitrary, well-formed JSON document stored as nvarchar value in SQL Server 2016. Is there a reliable way to do this? For ...

1
2 3 4 5
10