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
66
votes
5answers
153k views

How to turn json array into postgres array?

I have a column data that holds a json document roughly like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string (foo, bar). ...
34
votes
3answers
50k 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| +----...
32
votes
5answers
46k 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
21
votes
2answers
28k 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 --- ...
17
votes
6answers
48k 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 ...
14
votes
2answers
24k 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":...
13
votes
1answer
21k 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 ...
12
votes
3answers
35k 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 ...
12
votes
2answers
20k 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
1answer
33k 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 | [{"data"...
10
votes
1answer
30k 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 ...
10
votes
2answers
5k 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?
10
votes
1answer
13k 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). ...
9
votes
2answers
6k 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
29k 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
6k 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 ...
8
votes
1answer
13k 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
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
8k 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
1answer
6k 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"...
7
votes
1answer
6k 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
2answers
5k 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
439 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
2answers
8k 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 ?
6
votes
1answer
12k 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
13k 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
2answers
11k 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','...
6
votes
1answer
5k 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 ------------- ...
6
votes
3answers
5k 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 | ['George', 'John'] 2 | ['Thomas', 'James'] 3 | ['James', 'John'] How to list the number of bands each ...
6
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 ...
6
votes
2answers
8k 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 ...
6
votes
1answer
3k 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
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]')...
5
votes
2answers
134 views

Find Ancestry From JSON

I have a hierarchy that looks like this: As JSON in TSQL it is this: declare @Employees nvarchar(max) = '{ "person": "Amy", "staff": [ { "person": "Bill" }, { "person": "Chris", ...
5
votes
2answers
5k 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 ...
5
votes
2answers
2k views

How can I implement full-text search on a single value in a JSONB column?

I have a table that stores a conversation between two people. The data will look something like this: CREATE TABLE foo AS SELECT $$[ { "user": 1, "timestamp": 1, "message": "First message" }, ...
5
votes
1answer
7k 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*/ "...
5
votes
2answers
3k 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 ...
5
votes
1answer
402 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('{...
5
votes
2answers
2k views

JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

SQL Server 2016, I'm attempting to work with some regular data and return a JSON object for processing by another system. The other system does not recognize the array wrapper, and so I am attempting ...
5
votes
1answer
13k 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 ...
5
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 seens like a flexible ...
5
votes
1answer
211 views

Columnstore index for Key/Value Pair table on SQL Server

I need to build a database for storing assessments. Each assessment can have an indefinite number of questions, and an indefinite number of responses. Responses for each assessment can grow up to ...
5
votes
1answer
1k views

PostgreSQL converting undefined JSONB fields into a row dynamically

I have a table with a field of type jsonb, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance: From CREATE TABLE v(id,jsonb) AS VALUES ...
5
votes
1answer
1k views

JSON aggregation in a self-join query

I have a database in SQL Server. I need to make a join query on a table itself, the menutable. However, the left table is missing altogether from the result set. I only get the results from the right ...
5
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 -- ...
4
votes
4answers
38k 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. ...
4
votes
4answers
12k 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) ...