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

578 questions
109
votes
5 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,283
  • 2
  • 10
  • 8
58
votes
8 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,509
  • 5
  • 15
  • 26
54
votes
3 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
  • 541
  • 1
  • 4
  • 3
45
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,321
  • 4
  • 15
  • 26
36
votes
2 answers

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 --- ---- 1 {'name': 'n1', 'addr': 'ad1'} 2 …
AliBZ
  • 1,509
  • 5
  • 15
  • 26
29
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
  • 981
  • 4
  • 10
  • 12
28
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,177
  • 4
  • 15
  • 28
27
votes
1 answer

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 grp VALUES ('{"name":"First","arr":["foo"],…
BartZ
  • 373
  • 1
  • 3
  • 4
25
votes
1 answer

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
  • 382
  • 1
  • 3
  • 9
24
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
  • 373
  • 1
  • 2
  • 6
23
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
  • 551
  • 1
  • 3
  • 7
21
votes
3 answers

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
  • 211
  • 1
  • 2
  • 4
21
votes
1 answer

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
  • 211
  • 1
  • 2
  • 3
18
votes
3 answers

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 NOT NULL DEFAULT 0, created timestamp with time…
Sebastien Deschamps
  • 181
  • 1
  • 1
  • 3
18
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
  • 305
  • 1
  • 2
  • 6
1
2 3
…
38 39