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
1
vote
2answers
28 views

Update all values for given key nested in JSON array of objects

I have a jsonb column data in a Postgres table my_table. It contains the following data: [ {"id":"1","status":"test status1","updatedAt":"1571145003"}, {"id":"2","status":"test status2","...
0
votes
0answers
12 views

Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?

I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users The API json is 4 layers deep before I get to the items ...
0
votes
0answers
18 views

BigQuery - export JSON with null columns

I have table in BigQuery with structured data - hence to export it I need to use JSON format. Default BQ export to JSON behavior (not to say "a bug") is to omit all columns with null values. But in my ...
2
votes
1answer
29 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 ...
0
votes
0answers
30 views

Advantages and disadvantages of JSONB columns

I love using json columns in PostgreSQL, but I'm wondering if there are scenarios where jsonb would make more sense. For example, we store a "seen by" state in our messages table and need to use ...
-4
votes
1answer
91 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
26 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
58 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
1answer
28 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
61 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
40 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
88 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
17 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
0answers
23 views

Converting User-Defined Datatypes to JSON in Oracle

I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application: CREATE OR REPLACE TYPE udf_typ AS OBJECT ( name VARCHAR2(4000), value VARCHAR2(...
0
votes
1answer
28 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
23 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
31 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
65 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
42 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
13 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
126 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
580 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
31 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
16 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
34 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
34 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
33 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
27 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
58 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
133 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
182 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
30 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
206 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
45 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
11 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
37 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
48 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
151 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
151 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
241 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
13 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 ...
2
votes
1answer
54 views

Index on JSONB not improving query speed

table structure Column | Type | Collation | Nullable | Default ------------+--------------------------------+-----------+----------+------------------------...
2
votes
0answers
32 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
2answers
133 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
354 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
35 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
61 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
465 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 ...