Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit 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.

0
votes
0answers
9 views

Sum(JSON_EXTRACT) MariaDB 10.3.11

I'm looking so long, and cannot find anything right concerning - ho to sum the values from JSON column. My values in the column looks like this: [{"file":"FILE.doc", "POSITION":"3245", "NUMBER":"...
0
votes
2answers
20 views

Complex JSON query

I have a table like that: id | lat | lon | date | speed 1 | 45 | 66 | 2018-02-01 | 139 1 | 44 | 63 | 2018-04-01 | 129 2 | 41 | 56 | 2018-01-01 | 109 2 | 44 | 45 | 2018-04-01 | 99 I ...
2
votes
1answer
30 views

JSON Query in PostgreSQL

I have a table: CREATE TABLE foo (id,lat,lon) AS VALUES ( 1, 34, 45 ), ( 1, 45, 56 ), ( 2, 56, 67 ), ( 2, 58, 64 ); How can I get JSON like that: {"1":[[34,45],[45,56]], "2":[[56,67],[58,64]...
-1
votes
1answer
20 views

Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items?

Let's say I have some table A which have json-field items: A: foo: 'foo' items: [ {a: 1, b:2}, {a: 3, b:4}, ... ] There could be couple of thousands of items in one A object. Is there any ...
1
vote
0answers
23 views

Get JSON with where condition mysql

I have JSON value in column : INSERT INTO `bt_product` (`_Price`) VALUES ('{\"245\":\"10\",\"366\":\"5\",\"688\":\"1\"}'), ('{\"26\":\"1\"}'), ('{\"32\":\"1\"}'), ('{\"52\":\"1\"}'), ('{\"42\":\"1\"}'...
0
votes
0answers
25 views

Filter rows by objects in jsonb array in PostgreSQL

I have a json document for each row in my table (PostgreSQL 10) with the following structure { "partyboat": { "participants": [{ "name": "Bill Lambeer", "role": "Lessee" }, { ...
2
votes
1answer
26 views

How to send only updated values of a row?

CREATE OR REPLACE FUNCTION notify_col() RETURNS trigger AS $BODY$ BEGIN PERFORM pg_notify('channel', row_to_json(NEW)::text); RETURN NEW; END; $BODY$ LANGUAGE plpgsql ...
0
votes
1answer
33 views

How do I modify a JSON object?

I have a table "raw_data" in which I have a field "data" containing a json array like: {'a' : 1, 'b' : 2, 'c' : 3} I would like two different things: Append a new item like {'d' : 4} at the end of ...
2
votes
1answer
55 views

PostgreSQL JSONB update array elements query

Given a table in PostgreSQL 10: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"id":"a uuid_v1 here", "widgets": [ {"id":"1", "permissions": ["user1", "...
1
vote
1answer
21 views

How to prepend an element to a JSON array?

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty). How ...
-1
votes
0answers
17 views

is it possible to call a stored procedure within a json_object function in mysql?

I am trying to execute something like this: select json_object ('key', call new_procedure(in id integer(45),out json))) However it gives an error. Is there any workaround to solve this situation? ...
-1
votes
0answers
21 views

Optimal way to store json

I would like to store JSON in a database. I know that most DBs offer some sort of indexing in case of querying based on a value in JSON but I will only rarely need this. What is the optimal way to ...
0
votes
1answer
34 views

merge json online postgresql 10

I have the following tables: create table tags ( id bigserial primary key, file_id bigint, key varchar, num bigint, str varchar) create table meta_info ( ...
1
vote
1answer
23 views

Mysql Regex on where clause

I need a regular expression which will give me only those records which match a pattern.Below is table structure auto_id (Auto-increment) | json_string (Text) Sample records are: auto_id | ...
0
votes
1answer
33 views

Postgres jsonb complex query

I have a table on PostgreSQL 10 that looks like: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "...
0
votes
1answer
57 views

Performing wildcard search using the json_contains function in mysql 5.7 and above

I have some nested json data that i get using the json_object() function. Now I need to to check whether a certain value exists for a particular key which I am using as follows in my query:- select ...
0
votes
1answer
114 views

NodeJS problem with utf8_general_ci

I have a website with a chat system. This chat system has a table 'messages' and one of the columns is 'messagetext' set to utf8_general_ci(MySQL). In my website, I use php to query database. The ...
2
votes
1answer
71 views

Mysql temp table insert from json can't handle null values

The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table. I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug. ...
2
votes
1answer
41 views

How to “insert into select from” skipping exceptions

1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle. I will need to report on JSON ...
0
votes
1answer
32 views

Update postgres array jsonb

I'm using postgres 9.6, and my question is about updating a jsonb array column. My table looks more or less like that: ID Name MyArray 5 John [{"key":1,"key2":a}] 6 Mike [{"key":4,"key2":b}] ID ...
1
vote
0answers
34 views

Simplifying database structure with many unequal vectors

I want to find an efficient way to represent the JSON objects below. I made an ERD diagram to represents the JSON objects. This diagram below is a small piece within a much larger structure. There ...
1
vote
1answer
18 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
0
votes
1answer
26 views

Postgres JSON output using specific column as JSON key

I have data in Postgres (10.4) in the form of id | my_json ----------------------------------- 1 | {"a": 1, "b": 2, "c": [1,2,3]} 2 | {"a": 3, "b": 5, "c": [2,3,4]} I can output the data as JSON ...
0
votes
1answer
63 views

Postgresql JSONB Column, how to query for multiple specific elements in a JSON array

Giving the following JSONB data structure of a stopInfo column in a myTable table: { "stopInfo": [ { "stopType": "Origin", "partnerId": "KR01", "locationId"...
2
votes
1answer
67 views

Handling variable fields in a CSV file with JSON?

Im trying to figure out the engineering to get a CSV file into PostgreSQL. Requirements: The CSV has X number of predetermined required fields The CSV file has Y number of dynamic fields Example: ...
0
votes
1answer
106 views

Trigram index on all values of a JSONB column

I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6. ...
1
vote
1answer
252 views

Aggregate a JSON doing an average on values without defining keys

I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's ...
0
votes
0answers
58 views

How can I increase the UPDATE performance for jsonb fields? PSQL 9.6

I have a relatively large table, with less than 10 fields, containing close to a million rows currently but it will accumulate more than 10-15 millions in the near future. One of the fields is: jsonb,...
2
votes
1answer
266 views

How to sum (aggregate) all the values in a key/value JSONB?

in Postgres 10 I have the following table: CREATE TABLE testtable ( id int PRIMARY KEY, qty jsonb ); INSERT INTO testtable (id,qty) VALUES ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "...
0
votes
1answer
18 views

How to work with JSONB column

I use Postgres and jsonB column I have the following table: id(VAARCHAR(64)) | value(JSONB) -------------------------------- 1 |[{"x": "10", "y": 1}, {"x": "32110", "y": 12}] X has ...
0
votes
0answers
120 views

JSON_SEARCH, mysql 5.7 vs mariaDB

I am working with the following two machines and databases: Machine 1 Fedora 26 Server version: 5.7.22 - MySQL Community Server (GPL) Machine 2 Debian 9 Server version: 10.2.16-MariaDB-10.2....
1
vote
1answer
520 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 '{}'::...
0
votes
1answer
87 views

How make recursive json on mysql

my table looks like CREATE TABLE `test_comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, `test_id` int(10) unsigned NOT NULL, `user_id` int(...
3
votes
2answers
536 views

Cursor causes SSMS to crash

I have a cursor that generates one record of JSON text from a group of tables. The cursor has been making SSMS crash. The script runs for a time then SSMS fails. Below is the code that I have ...
1
vote
1answer
146 views

Transform/map json object array to primitive for GIN indexing

Say I have a table with a jsonb column that contains data structured like this: { "records": [ { "id": 1, "tag": "address" },{ "id": 1, "tag": "receipt" } ] } ...
0
votes
0answers
35 views

JSONB: function which sorts only JSON arrays

I've just finished SQL code which checks if particular "query" fulfils "conditions" stored in rows. If yes, id-s of that rows are returned. This is made up example. CREATE TABLE jsonb_test ( id ...
0
votes
0answers
149 views

Update jsonb array without index in postgres 9.6

I have a json array in a database field with name filters in the following format '{"org":[219,160,125]}' I need to update the array to '{"org":[219,160]}' using query for all rows in the database. ...
0
votes
1answer
156 views

How to query inside jsonb column

I have a column type of jsonb named changes THis is the structure of my column. [["change","is_enabled",[false,true]]] I want to query within the column. I want to find all the rows that "...
0
votes
1answer
74 views

sql server for json

I want something like this { "customer":[ { "custid":1, "companyname":"Customer NRZBB", "order":[ { "orderid":10643, ...
0
votes
2answers
295 views

Best SQL datatype for JSON

In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar. Most of our JSON strings will not use more than 100 characters. Should I use nvarchar(100) or nvarchar(max)? We prefer ...
3
votes
2answers
2k 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 ...
1
vote
1answer
449 views

MariaDB compare JSON

How to compare two JSON fields in MariaDB? MariaDB 10.2 and 10.3 - FAILS SELECT CAST('{"q":2}' AS JSON); Returns : SQL Error (1064): You have an error in your SQL syntax; check the manual that ...
1
vote
2answers
532 views

How to dump to csv valid json with Postgresql?

I am trying to dump some data as json with: \copy (SELECT json_build_object('areaSqft',area_sqft, 'titleNos',title_nos, 'buildingIds',building_ids, 'geometry',geom_json) FROM my_data) to my_data.csv ...
3
votes
1answer
1k 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 ...
3
votes
1answer
41 views

How to retrieve the value of deleted key in jsonb?

Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key? Thus, if the key was not found, a null is returned or ...
2
votes
1answer
3k 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 ?
1
vote
1answer
26 views

Postgresql 9.5 JSON problems

I have to update a complex query that takes JSON fields: SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType from SELECT context->>'Source' as Product, ...
0
votes
2answers
2k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
2
votes
1answer
1k views

Multiple rows into json

Looks my question is unusual because I didn't find the answer at all. Let's imagine, I have table A with columns: language and uri. language | uri ---------|----------------- ru | ...
1
vote
1answer
46 views

Query JSON in Azure SQL DB to get one array element per row

I have a stored procedure in a SQL Azure DB instance, and the input parameter is a JSON string formatted like this: { "OrderNumber":["OD12034"], "Site":["New York"], "LineNumber":[1000,...