Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

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. JSON supports all the basic data types you’d expect: numbers, strings, and boolean values, as well as arrays and hashes.

References

JSON

669 questions
146
votes
8 answers

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, bar'). That would be easily possible with the…
Christoph
  • 1,653
  • 2
  • 12
  • 8
72
votes
9 answers

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
  • 1,827
  • 5
  • 17
  • 27
60
votes
7 answers

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 JSON object? I have read about ROW, but it returns a…
Yanick Rochon
  • 1,651
  • 4
  • 20
  • 28
56
votes
4 answers

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| +--------------------------------------+ |foo | …
tuxlu
  • 561
  • 1
  • 4
  • 3
51
votes
2 answers

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", "addr": "ad1"} 2 {"name": "n2", "addr":…
AliBZ
  • 1,827
  • 5
  • 17
  • 27
51
votes
2 answers

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
  • 1,721
  • 4
  • 15
  • 12
36
votes
2 answers

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":1, "children":[]}'), ('{"parent":2, …
Kokizzu
  • 1,403
  • 6
  • 18
  • 35
35
votes
3 answers

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 on StachExchange?
Hvisage
  • 483
  • 1
  • 4
  • 6
35
votes
1 answer

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); INSERT INTO grp VALUES …
BartZ
  • 453
  • 1
  • 4
  • 4
34
votes
4 answers

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 have the same but without the quotes: one two Looks like I can't use…
Maxim Yefremov
  • 465
  • 1
  • 4
  • 7
29
votes
2 answers

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). Now, supposed it contains two records: 1, { name:…
Golo Roden
  • 422
  • 1
  • 5
  • 9
26
votes
1 answer

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" : ["some", "data#1"], "date": "2016-04-21"},…
Klemen
  • 591
  • 1
  • 4
  • 7
25
votes
6 answers

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. The columns are regular Varchar, Int, etc.…
johnny
  • 612
  • 3
  • 11
  • 17
25
votes
2 answers

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 tablename where id = "1" I'm not able to get the…
Rabi C Shah
  • 251
  • 1
  • 3
  • 3
24
votes
1 answer

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": "Bob","pos":"Manager","age":42 } I want to…
Chenko47
  • 371
  • 1
  • 4
  • 8
1
2 3
…
44 45