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.

0
votes
1answer
7 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
1
vote
1answer
24 views

Index on JSONB not improving query speed

table structure Column | Type | Collation | Nullable | Default ------------+--------------------------------+-----------+----------+------------------------...
2
votes
0answers
15 views

Query plan (JSON format) in pgAdmin not showing completely [duplicate]

I would like to use the tool Postgres Explain Viewer (PEV). For it I need to paste the execution plan in json format. If I try to get the execution plan in pgAdmin with this query: EXPLAIN (ANALYZE, ...
0
votes
0answers
20 views

Mysql: Insert into JSON column type with GROUP_CONCAT

I'm trying to insert a list of words into a column with the JSON type: create table my_table ( ... synonyms JSON ... ) My query looks like this: INSERT into my_table (id, synonyms) ...
2
votes
2answers
19 views

How to detect whether a key is present in a JSON object using TSQL?

This is an interesting one. Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d. Imagine this object is passed to a stored procedure and every key is ...
0
votes
1answer
23 views

Mysql 5.7 - select where json

I have some JSON data stored in a filters column. An example from one row: name | filters ---------+---------------------------------------------------------- John Doe | [{"Year":2004, "SportID":...
0
votes
0answers
22 views

Select JSON elements from nested documnet

I have a JSON document such as: { "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } } Here is the table definition: CREATE TABLE "v22"."assets_exp" ( "_id" ...
4
votes
1answer
220 views

Why does JSON_QUERY within CASE statement fail to remove escape characters?

It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement. Example code below. declare @data ...
2
votes
1answer
50 views

Did adding JSONB indexes bloat the database?

We are running: user@host:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------...
-1
votes
1answer
18 views

Postgres jsonb column or extra table for tightly coupled data? Which will likely have better performance [closed]

I am working on some sort of a bug tracking product. We're using Postgres as our only database. So far we are only using normal tables and no jsonb. Our basic data hierarchy looks like this: ...
1
vote
1answer
28 views

Using both DISTINCT and ORDER BY in json_agg

I'm trying to get a sorted aggregate of unique values in postgresql. SQL Fiddle The result should be a json array of unique objects, sorted by index field. The value field can be used as unique key ...
1
vote
1answer
24 views

Postgres: flatten Key-Value (K-V) store with jsonb_agg

I have two tables: bar and foo (one to many respectively). The foo table has an internalId and an external reference. The issue here is that the external reference can be duplicated. I want my ...
2
votes
2answers
47 views

Define JSON Index for Schemaless using MySQL 8.0

We are using MySQL 8.0 and need to implement Schemaless Feature where we allow Vendors to create a Module(ObjectName) and add columns(int, string, currency) to it. Vendor can add objects after ...
0
votes
1answer
22 views

Storing data as JSON vs meta table

I am using MySQL 5.7.26 and have a very large table with multiple foreign keys for a column. I can store it with JSON type in a single table - tours (Case A) or by creating another table - like metas (...
0
votes
0answers
8 views

How to Use LOAD DATA to import JSON data and specify date data type on import

I'm importing JSON data in sql and would like like to convert human readable date data to yyyy-mm-dd format. Here's the flow: I'm using the following json file stored under /home/name/json_data/...
0
votes
0answers
21 views

PostgreSQL: join by dynamic nested JSONB attribute

I'm having quite an unusual task and I'm not even sure if it's solvable. I have a table with JSONB column which can contain arbitrary nested JSON objects. Let's call it objects table. And I have a ...
0
votes
0answers
22 views

Map PostgreSQL JSON to view table

I have the following table with some JSON data | id | from | to | |-----|:------------------------...
0
votes
1answer
29 views

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

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
28 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
33 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
37 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
21 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 ...
0
votes
0answers
9 views

How to always select records in array or empty array

I have a field records of jsonb[] type and I need to insert records from other tables(has many relation) whenever there is an update via trigger. When I apply select SELECT INTO records * FROM "...
0
votes
0answers
39 views

PostgresSQL update specific property in specific object inside an array

Ehi all :) Using the postgresql as document store, but now I'm having some issues on setting a specific property. This is my json. { "Id": "1d8a9c43-5ece-44f9-b468-c1c87cdef47a", "Input": { ...
5
votes
1answer
315 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('{...
1
vote
1answer
42 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
27 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
22 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 ...
2
votes
2answers
34 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
34 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
91 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
19 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
37 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
116 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
115 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
155 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
31 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
19 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
39 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
159 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
19 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
238 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
77 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
47 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
65 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
59 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
14 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
20 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
23 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
295 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 ...