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
2
votes
1answer
918 views

Convert a row to a nested JSON document containing all relations

In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the ...
-4
votes
1answer
260 views

Earliest version of SQL Server with OPENJSON

What is the earliest Microsoft SQL-Server version having the T-SQL OPENJSON function? As far as I could see before asking this question, the Microsoft manual shows the function's documentation for ...
0
votes
1answer
301 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
0
votes
1answer
881 views

Passing Variable in json_value in sql server 2016 [duplicate]

declare @id int set @id = 1754 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber, (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+...
0
votes
2answers
562 views

pgadmin: can't insert jsonb[] values with strings

I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "...
0
votes
2answers
68 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
0
votes
1answer
2k views

Postgresql, export to json

Version: PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1). Question: Is possible export to json file? Something like csv export. COPY ( SQL ) TO stdout DELIMITER '"' csv; Thanks. Update: Or can I add ...
0
votes
0answers
676 views

Is there any way to query postgres JSON/JSONB columns with ilike and indexing?

I have a table called user_data where I have JSON column called data which can have any key that can be given by application users. Here I wanted to do ilike query with using proper index. Since keys ...
0
votes
1answer
54 views

Postgresql and jsonb: perform a like over all values of an array

Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email: ["email1@foo.com", "email2@foo.com", "email3@foo.com"] I want to be ...
0
votes
1answer
409 views

How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement

My Query is declare @id INT set @id = 69 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber, (JSON_VALUE(cast(ad.data_map as varchar(max)),'$....
2
votes
1answer
53 views

Gather values from different related rows with JSONB fields

Say I have the following table structure: jobs +----+------------+ | id | some_field | +----+------------+ | 1 | some_val | | 2 | some_val | | 3 | some_val | +----+------------+ events +----...
0
votes
1answer
481 views

Accessing nested array in json blob in PostgreSQL

I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following: { "id" : "5", "data" : "[[1,2,3],[2,3,4],[4,5,6]....] } My query needs ...
-1
votes
2answers
402 views

EAV in postgresql

I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
-2
votes
1answer
362 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
0
votes
1answer
16 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: ...
5
votes
0answers
2k 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 ...
7
votes
2answers
3k 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
1k 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
1answer
682 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. +----+------------+----------+----...
1
vote
1answer
195 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
105 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
190 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
32 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
4answers
3k 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 ...
3
votes
1answer
979 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
42 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 ...
2
votes
1answer
2k 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
920 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
1answer
120 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
37 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
1k 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
238 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
2k 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
113 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 ...
3
votes
1answer
160 views

Index on JSONB not improving query speed

table structure Column | Type | Collation | Nullable | Default ------------+--------------------------------+-----------+----------+------------------------...
2
votes
0answers
192 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
3answers
466 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) ...
3
votes
2answers
4k 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
679 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
157 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" ...
5
votes
1answer
1k 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 ...
3
votes
1answer
231 views

Did adding JSONB indexes bloat the database?

We are running: user@host:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------...
-1
votes
1answer
138 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: ...
2
votes
1answer
4k 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
240 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
93 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
346 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 (...
2
votes
0answers
981 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
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"') ...

1 2 3
4
5
11