Stack Exchange Network

Stack Exchange network consists of 175 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.

1
vote
0answers
15 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
0answers
10 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
12 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
25 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 ...
1
vote
1answer
26 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
28 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
0answers
9 views

How to model data in DB (PostgreSQL) for SNMP-like multiple configurations

I am working on product managing and monitoring Network (NMS-like products). Product manages configuration of network devices, for now each device has stored its configuration in simple table - this ...
0
votes
0answers
43 views

How database schema should look like for app like to spreadsheet, airtable, notion.so? [closed]

I am a front-end developer and I am curious how airtable.com or notion.so (spreadsheet) store their data in the backend. Is it a NoSql or SQL solution, or maybe something in between such as Mysql with ...
0
votes
0answers
12 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
25 views

Postgres select join json querry

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
18 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 ...
2
votes
1answer
35 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
34 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
42 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
27 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
10 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
10 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
19 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
20 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
95 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
108 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
94 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 ...
1
vote
1answer
33 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
30 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
20 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 ( ...
0
votes
1answer
58 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",...
0
votes
1answer
31 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
204 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 ...
0
votes
0answers
21 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
31 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
44 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
27 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
32 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
35 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
41 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
40 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
155 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
52 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
120 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
76 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
35 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
134 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
354 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
227 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
43 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
126 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
35 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
22 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
34 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
124 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"...