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
9 views

What's best practices for changing a user's email in an application?

I'm working on a crud for an email application, and I'd like the ability to change "everything". Right now, however, it matches the JSON string to the Database based on the email address. Problem: ...
0
votes
0answers
10 views

How to control how JSON_ARRAYAGG orders results

I am testing MySQL JSON_ARRAY and JSON_ARRAYAGG functions in order to move some application expensive processes to SQL stored functions. In the following e-commerce application example, I am trying ...
6
votes
2answers
373 views

Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]

Frequently in Javascript you'll have something like [ 7,2, [6,7], 2,10 ] How would you query that structure with OPENJSON I would like this, 0, 7 1, 2 2, 6, 2, 7, 3, 2 4, 10 I'm having a hard time ...
0
votes
1answer
15 views

PostgreSQL: Find row with JSON object attribute in array

I have a postgres database which is set up like this: CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"...
0
votes
0answers
9 views

Database design for arrays - utilize relational database

I am trying to build an application, where users can create unlimited "parsing fields", that will contain either a: String Array id | name | type ------------------- 1 | name | string 2 | table |...
0
votes
1answer
22 views

Append JSON array to an object in PostgreSQL

I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure. +----+------------+----------+----...
0
votes
0answers
8 views

How to get json object from json array where one of it's value fulfill assumption

In mysql I have json array like this [{"amount": "53.00", "paid_at": 2019-05-10, "payment_date": "2019-05-16"}, {"amount": "53.00", "paid_at": false, "payment_date": "2019-06-16"}, {"amount": "53.00"...
1
vote
1answer
18 views

Use jsonb array inside where IN or ANY in PostgreSQL

I want to select all products that have active shipments (those have one or more shipment_ids in shipments table). My product table has a jsonb column called data like this: { "name": "foo", ...
0
votes
1answer
22 views

What is the best way to unpack JSON objects in SQL Server?

Part 2 of my foray into JSON, I'd like to add a user to multiple groups: insert an JSON array into a table. Ideally, the JSON would look like this: '{ "Email": "WMogh@starfleet.gov", "...
0
votes
1answer
26 views

What's the low-down on MariaDB JSON support?

Does MariaDB ever plan to have full JSON support or is it just going to be an alias forever? It's a bummer to set constraints definition in the table just to make it work properly. Application-level ...
0
votes
0answers
28 views

What is the best way to update a table from a JSON string?

I'm fiddling around with JSON and SQL, and I need to update a record based on a new JSON string. Here's what I've got, but to be honest, It seems like an awful lot of code: create procedure ...
0
votes
1answer
26 views

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of ...
2
votes
1answer
37 views

Return all rows containing specific value in json array

I have the following table: CREATE TABLE [dbo].[Journals]( [Id] [nvarchar](450) NOT NULL, [CorrelationId] [nvarchar](450) NOT NULL, [Version] [int] NOT NULL, [Type] [nvarchar](max) ...
0
votes
0answers
18 views

What is the proper way to index a column in MySQL that has a lot of json data in it?

We have a long row that includes a json column that holds variable columns specific to each row. We currently do not index that column as we don't query against it - rather it is supporting data for ...
1
vote
1answer
49 views

flatten JSON array into comma delimited list

I am trying to construct a SELECT statement which will take a JSONB column in the following format: { "Cities": [ { "Name": "Atlanta" }, { "Name": "London" }, { ...
0
votes
1answer
24 views

How to filter a value in json field type on Postgres 9.2?

My json field data is like this: {"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},...
0
votes
0answers
9 views

how to get data from multi table with column json in postgresql?

I have a lot of tables like the following tables. main_table id |customer_id | tabl2_id | tabl1_subJson_id |... ---|------------|-----------|------------------|------ 1 | 1 | 1 | ...
0
votes
0answers
14 views

Select / remove nested JSON attributes postgresql

I have the following JSON structure in postgres. drop table if exists data cascade; create table json_test (data json); insert into json_test values ('{ "a": "1", "b": "2", "c": "3", "level2"...
0
votes
1answer
39 views

Error trying a manual's example in DB2 11.1.4.4

When trying this example from the DB2 11.1 manual: VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER)); I get this error: SQL0171N The statement was not processed because the ...
0
votes
1answer
31 views

Unstructed data field: Query all values from array of objects by key

I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like: "nickname": "value" "fullName": "value" "office": "value" "unity": "value" and a ...
0
votes
1answer
50 views

How to unnest and display elements of a JSON array?

i have this table : And i want display all the names in the table like this : Also i want to list the number of gender each genre : Desired output:
5
votes
2answers
129 views

Find Ancestry From JSON

I have a hierarchy that looks like this: As JSON in TSQL it is this: declare @Employees nvarchar(max) = '{ "person": "Amy", "staff": [ { "person": "Bill" }, { "person": "Chris", ...
2
votes
1answer
27 views

Properly escape characters in a string for JSON

With the below example, how do I escape the double-quote characters that are in the @data variable? declare @data nvarchar(max) = N'"TEST"'; declare @jsonFragment nvarchar(max); declare @id int = 999;...
0
votes
1answer
8 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
39 views

Index on JSONB not improving query speed

table structure Column | Type | Collation | Nullable | Default ------------+--------------------------------+-----------+----------+------------------------...
2
votes
0answers
17 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, ...
1
vote
1answer
44 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
29 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
25 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
44 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
281 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
76 views

Did adding JSONB indexes bloat the database?

We are running: user@host:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------...
-1
votes
1answer
20 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
93 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
33 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
52 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
28 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
19 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
39 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
24 views

Map PostgreSQL JSON to view table

I have the following table with some JSON data | id | from | to | |-----|:------------------------...
0
votes
1answer
52 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
29 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
38 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
83 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
24 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
12 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
41 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
368 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
164 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
28 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 := ...