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
0
votes
1answer
264 views

Multi-master (NoSQL) database solutions with cloud and no-premise option [closed]

We are currently investigating database options for distributed database (possibly globally distributed) where the data in general is stored in "cloud" (this would not be a special requirement). The ...
1
vote
1answer
43 views

How to sum of column by depending other column in my sql?

how to calculate / SUM of column by the latest data of updateid column ? I try this : SELECT * FROM yourTable; SELECT ANY_VALUE(code) AS code, COUNT(*) AS total_rows, SUM(data->'$.chart."21"') ...
0
votes
1answer
244 views

How to update an element in json array with where clause?

I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings. Here are some sample json values from jsonb column of the ...
1
vote
2answers
1k views

sqlite Json grouping json_each

I want to generate json grouping users by year. I have got a "user" table in sqlite with the following columns: date name surname value I would like to create a JSON with the following structure: ...
1
vote
1answer
856 views

update json value to null

As I understand it, if you select json_value or json_query specifying a key or object that does not exist in your json document, then in strict mode, you will receive an error. This allows you, for ...
6
votes
1answer
4k 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('{...
0
votes
1answer
939 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
0
votes
1answer
356 views

Getting the contents of a json file as a variable in plpgsql?

I have a function based this post by Sean Huber in order to get the content of a file: CREATE FUNCTION file_read(file text) RETURNS void AS $$ DECLARE content text; tmp text; BEGIN file := ...
0
votes
0answers
25 views

I need some help about indexing a JSONB column

I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it. Some of the fields are being indexed already, but there's a ...
3
votes
2answers
269 views

UPDATE with CAST(meta->>“$.field”) failing due to “null”

This issue is affecting MySQL 5.7.25. So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At ...
0
votes
1answer
161 views

How to eliminate for loop in a nested aggregate problem

I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
0
votes
2answers
6k views

How to group multiple ROW as ARRAY

This is my current Query: SELECT innings_no, Player_Name, sum(NoBalls) as NB FROM ( SELECT A.innings_no, A.bowler, ...
1
vote
0answers
50 views

How to read jsonb or json in postgresql spi?

How can I read a json/jsonb field in a PostgreSQL server side function? I am trying to write a server side function using C and SPI for PostgreSQL 11. The json field in mind is simple and contain ...
0
votes
1answer
325 views

Group json data in db using a query

I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really ...
0
votes
1answer
2k views

Postgresql SELECT item from JSONB array - array as top-level json object

I have JSONB column which stores the data in this format [ { "item1": "value1", "item2": "value2" }, { "item1": "value3", "item2": "value4" } ] I am ...
0
votes
1answer
2k views

change mysql datatype column from string to json

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON. I ...
3
votes
1answer
5k views

JSON_VALUE in SQL-server 2014 [closed]

I am looking for an equivalent functionalities of JSON_VALUE in SQL Server 2014. Can any of you post an alternate of the below code which is supported in SQL Server 2014 SELECT JSON_VALUE(ERROR_MSG, '...
1
vote
2answers
372 views

How does ON DELETE CASCADE relate to JSON?

For relational databases, there is the notion of ON DELETE CASCADE. When working with JSON, how do these two concepts relate? Is ON DELETE CASCADE supported for JSON? Can you give an example of a ...
0
votes
1answer
2k views

How to import json table into PostgreSQL table?

I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data: [{ "token": "a978f9793a594d11860e9cf7962996d7", "name": "vehicle.moving", "description": "...
0
votes
1answer
2k views

Postgres select join json query

I have a problem to make a postgres query with join from data of json. I have 2 tables The first one is media: id as integer path as string and an other table playlist id as integer media as ...
0
votes
1answer
34 views

Does any MariaDB engine have the option to declaratively or configuratively abstract highly-redundant data?

I have an application which is architected in a "NoSQL" style around one fully denormalized "main table" which currently just holds a primary key and one JSON-valued column. For reasons which are ...
7
votes
1answer
9k views

Loop through key/value pairs of a jsonb object in postgresql function

I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object. create or replace function myFunction (input jsonb) returns jsonb as $$ BEGIN // ...
0
votes
0answers
207 views

PostgreSQL select jsonb key value from an array while joining table

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values. -- NOT WORKING SELECT ...
0
votes
1answer
203 views

Select All Instances of Substring in a String

I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId ...
0
votes
1answer
243 views

Efficient way to compute unique values using a PostgreSQL GIN index

Say I have a JSONB column, with a GIN index on it. I can use jsonb_object_keys() to fetch the keys for each row. But is there an efficient way of getting the unique set of keys, across all rows? ...
0
votes
1answer
298 views

Query Quill-Delta JSON array in Postgres

I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g. ----------------------------------------------------------------- | id | title | content ...
1
vote
0answers
71 views

Joining on nearest timestamp in MongoDB

MongoDB version: 3.2.11 Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To ...
-1
votes
1answer
30 views

Can't compare value of a JSON key with a text value

SELECT id, body->>'process_code' as process_code, body->'filePaths' as files FROM masters."PROCESS" where body->'filePaths' @> '[{"access_type": "Public"}]' or body-...
0
votes
1answer
48 views

get inherit keys inside json

I have JSON field with values like in the example, and I need to get all data from the key 'stat' inside it. The list of keys inside the data not constant. WITH test_data AS ( SELECT 1 as id, '{ ...
0
votes
2answers
2k views

How to Insert JSON data to BYTEA column in the table in POSTGRESQL?

So, I came across the function script which is converting JSON data to BYTEA and then insert as a record in the table in a BYTEA column. (As I assumed what the code is doing) In Oracle the function ...
0
votes
1answer
2k views

Select multiple columns as JSON in a single query

How can I combine several columns (of varying types, e.g. int, datetime, nvarchar) as a single JSON-formatted expression in a select query? For example, I have columns col1, col2 and want the query to ...
5
votes
1answer
736 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 ...
6
votes
1answer
7k views

Extract JSON array of numbers from JSON array of objects

I have a table containing a json array of objects similar to this: id | record ____________________ name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...] I would like to get a table ...
1
vote
1answer
122 views

Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, ...
0
votes
1answer
43 views

Aggregated JSONB result in update query

Introduction I created a toy example to extract the problem into a smaller part. Here is the schema definition with two tables users and events, users can have multiple events: create table users ( ...
2
votes
1answer
4k views

How to use JSON path wildcards in JSON_QUERY?

For a minimal working example say I have a column storing JSON with a structure similar to the following. DECLARE @json NVARCHAR(4000) = '{"Dims": [ {"Name":"Apple", "Baking": ["Pie",...
2
votes
2answers
1k views

ERROR: invalid GeoJson representation

the GeoJSON Spec show, { "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" } } But when I wrap that in ...
2
votes
2answers
5k views

Get the max value from a slice of a JSON Array?

I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw. The limits Start & End of the slice are ...
1
vote
2answers
2k 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
50 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
82 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]...
0
votes
1answer
270 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
162 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\"}'...
4
votes
1answer
638 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
481 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
2k 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
411 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 ...
0
votes
1answer
768 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
312 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
144 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", "...

1
3 4
5
6 7
11