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
0answers
3 views

Preserve tab and line break while insert JSON string to Document in MongoDB

I'm inserting new document to MongoDB as following: db.crawl_api_config.insertOne({ apiHost: "logbasex.github.io", apiUrl: "20221", contentType: "Blog", ...
0
votes
0answers
8 views

JSON support on Azure Database for PostgreSQL

After migrating the DB from OnPrem Postgres DB to Azure SQL Database for PostgreSQL, facing issue that non of the JSON functions are working in the PaaS Db. Looks like JSON is not supported on PaaS ...
0
votes
0answers
37 views

Index for finding attributes in large JSONB array

Based on this original question and the follow up question from 2015. Suppose I have a jsonb column data within a table tracks. This column holds an array with multiple keys e.g. Artist and Title in ...
1
vote
1answer
26 views

Local JSON file vs MySql database [closed]

I've built a website using node js and it has two parts. one for users and one for website administrators for editing content on website that is locked by a login page. The user side, loads data from ...
0
votes
0answers
24 views

How to run query to convert undefined JSON fields into a row dynamically

Related to this answer I found a solution above to get undefined JSON files into rows. However, I need to run query twice so I modify the query to run it once. I tried to run the query returned from ...
-1
votes
1answer
20 views

Read specific fields from Postgres jsonb

I have data like {"name": "a", "scope": "1", "items": [{"code": "x", "description": "xd"}, {"code": &...
1
vote
0answers
33 views

EAV vs JSON/XML column approach to store complex user form data in a database? [closed]

We are working on an internal tool which will essentially be a complex, power-user oriented form app. It is not a dynamic field app - users can't define their own fields. Form field types: The form ...
2
votes
0answers
57 views

check if a list of strings in a jsonb contains (or not) an array of strings

Given a table "bgp"."interconnect": Query 1 SELECT array_to_json(array_agg(netelements)) FROM ( SELECT jsonb_object_keys("bgp"."interconnect"."pulse&...
0
votes
1answer
24 views

JSON Creation Issue

I'm new to PostgreSQL database. Although I've good experience on Oracle Database. I'm facing issue while generating JSON from Postgres. I've tried to generate the same output but not able to achieve ...
0
votes
0answers
22 views

Using GIN index for array and single value in the same key

We have table: create table last_games(id int not null primary key, name text not null, json_value jsonb); And filled with next values: insert into last_games values (1, 'Tournament1', '{"...
0
votes
0answers
19 views

Why does json_equal use so much PGA memory and how to avoid it?

I have an Oracle 19c database and a schema with two tables (let's call them L and R). They have the same structure: Two VARCHAR2 columns I'll call T and J. The second one has an IS JSON STRICT ...
0
votes
1answer
62 views

(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres

So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects. How I ...
0
votes
0answers
18 views

uploading json file to bigquery table

I have a file at Linux server which I have uploaded to gcs bucket location [xyz@innolx20122 ~]$ cat test.json ...
0
votes
1answer
48 views

Why is this query on JSON values in SQL Server using an index scan instead of an index seek?

I have a table with the following schema: CREATE TABLE [dbo].[Obj1Json]( [ObjectID] [int] IDENTITY(1,1) NOT NULL, [PointerToSource] [nvarchar](255) NULL, [CreateDate] [datetime2](7) NOT ...
1
vote
1answer
82 views

Dynamically update JSONB rows - POSTGRESQL

I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field. The node we are trying to move, however, is not ...
0
votes
2answers
93 views

Clever way to order a json item by key in postgresql (two examples)

I need to sort a json item in a record, so here are two examples: First case create table jsontable ( jsonitem json ); Next I insert a json item: insert into jsontable(jsonitem) values ('{ "...
0
votes
1answer
73 views

How to conditionally create JSON array in postgres with group by clause?

I am using Postgresql 13.0 and have a table category as follows: cat_id | cat_name | piece | weight | is_tag_stock -------------------------------------------- 4 | cat1 | 20 | 10 | ...
0
votes
1answer
93 views

How do I efficiently emulate JSON_TABLE in Mariadb?

I don't have MySql readily available, but from what I understand, the best way to achieve what I want in MySql is to make use of JSON_TABLE. Also, from what I understand, JSON_TABLE is being very ...
0
votes
2answers
62 views

Unpacking JSON into 14 columns without crosstab() and tablefunc?

In a Postgres 12 database, I have a simple schema containing a JSON column. The JSON will always contain an object, and inside the object there are always 14 keys like so: CREATE TABLE json_unpacking ...
1
vote
1answer
34 views

Optimize the DB select query JSON B type extract

We have a query which we want to optimize for high performance. The message column in table is of JSONB type and it holds a large json object. Is there a way to optimze this? We have an index already ...
1
vote
1answer
41 views

Mysql JSON update by value of a key

I have a table like: CREATE TABLE `campus_tb` ( `campus_id` int(11) NOT NULL AUTO_INCREMENT, `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`...
0
votes
1answer
45 views

query a JSON type column in Postgres

I have a table in Postgres and trying to query a JSON type columns data_meta & source_data request_id | bbb1-bdfa3ae765a2 job_type | Incoming data_meta ...
0
votes
0answers
13 views

Delete JSON object from array, in multiple columns, post

CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"AAA", "...
1
vote
1answer
54 views

Does PostgreSQL support wildcard indexes on JSON?

I have a multi-tenant service with a table like this: project_id | user_id | user_properties Each project belongs to a different customer and customers can freely attach metadata to their users. A ...
6
votes
1answer
182 views

Why is SQL Server spliting it's (JSON) response into multiple rows?

I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
1
vote
1answer
61 views

Storing dart throws in relation database (JSON vs multiple tables)

I'm trying to model database for storing dart throw practices. Data sent from frontend will look something like this [ {"target_name":"S13","singles":1,"doubles&...
0
votes
1answer
54 views

PostgreSQL json array of objects, default 0 values for properties if not found

So my query looks like this: select dk.product_id, dk.product_name, json_agg( json_build_object('location', dr.location, 'quantity', dr.quantity))::jsonb stock from dax.dx_k dk left join dax....
0
votes
1answer
14 views

Join on nested obj attribute in JSONB column along and a varchar column

I have table connections where both columns are varchar: +---------+---------------+ | user_id | other_user_id | +=========+===============+ | foo | bar | +---------+---------------+ | ...
0
votes
0answers
28 views

Index for the items inside a jsonb array in Postgres

I've got a table which looks like this: create table data ( id serial primary key, report jsonb ); with the data like this: insert into data (id, report) values (1, '[{"item_id": 1, &...
0
votes
2answers
67 views

psycopg2 connect error: cannot pass more than 100 arguments to a function?

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass ...
0
votes
1answer
143 views

Passing a variable in OPENJSON WITH statement in SQL Server

Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason ...
1
vote
0answers
26 views

JSONB encryption of specific elements

I have a JSONB object containing lots of elements and nested objects, and some of the elements are to be encrypted, e.g.: jsonb_obj = '{"obj1" : {"obj2": {"firstname":&...
0
votes
0answers
17 views

Analysis of Conditions with no results and high offset number - PostgreSQL JSONB [duplicate]

I am asked to record down the analysis on PostgreSQL JSONB query having Conditions with high offset number and query that provide no results considering the below. select * from sub_test df, ...
0
votes
0answers
26 views

PostgreSQL JSONB Execution Plan with high Offset

How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below "Test Query", help me to clarify the ...
0
votes
1answer
55 views

Count hours when a specific value is higher than a treshold

i have this JSON that is contained in "data" column, the table also have: insert time, pk and some fk as reported below: PK INSERT_TIME DATA SENSOR 1 2020-01-04 15:...
0
votes
1answer
118 views

Postgres JSONB - Flatten nested objects and groupings

As i've learned, if i need to flatten a JSON contained in a specific field of my postgres db table, i can use one of the following functions/operators Now, those functions are perfect for a simple for ...
0
votes
1answer
36 views

Cross join flatten JSON - flatten only one the most recent row

As reported in this question, i want to flatten a JSON starting from a nested JSON object that contains arrays like this one: port_name, port_description, port_status_int, port_status, port_speed_int, ...
0
votes
1answer
37 views

Search in JSON by id and name

I have this table: id number name text row for example: id name 1 {"vals":[{"id":123,"name":""}],"additional":"text"} 2 {"vals":[{...
1
vote
0answers
27 views

Extreme slow query for a simple indexed selection - Why? [duplicate]

i have a table in my database that is described as follows: -- Table: public.devicemeasurement_mqtt -- DROP TABLE public.devicemeasurement_mqtt; CREATE TABLE public.devicemeasurement_mqtt ( ...
0
votes
1answer
30 views

Mariadb json store with unicode \u sequences

I am using mariadb and I store json data with unicode encoded strings eg \u0395, in a medium text column name remoteData with collation utf8_general_ci. My problem is where query this column like that ...
0
votes
1answer
127 views

PostgreSQL - Flatten nested JSONB object array

Im my database with Postgres 11, i have this JSON in my rows stored in a JSONB field, and i need to flatten out the list of objects that are over "ports" key, so, instead of having a json ...
1
vote
1answer
289 views

PostgreSQL Query output to JSON

I would like to get the output of a particular Query from the postgresql server having joins of a multiple Tables with one to many relations in the json format to a particular file. The Query output ...
1
vote
1answer
247 views

How to sum the values ​of a json column filtered with regex?

How to sum the values ​​of a json column filtered with regex? Columns: A.name: Varchar with name product B.products: JSON with time active By product Query: select A.name as product, sum(((...
1
vote
1answer
275 views

Can I select keys stored in a postgres jsonb column as values dynamically

I have a JSONB column in a Postgres DB that contains some dynamic data about a row. Think of the table as a single table with an id column and a "dynamicData" JSONB column for simplicity for ...
0
votes
2answers
150 views

Update postgres json value by other attribute

I'm using PostgreSQL 9.6 and I got the following table: Cars ID Name Data 1 abc {"color":"red","wheels":4,"my-value":false} ...
0
votes
1answer
157 views

postgresql prepared statements with JSONB containment

I'm trying to write a prepared statement using a date held in JSONB. A prepared statement doesn't allow the containment operator @> so I used the function name itself jsonb_contains which executes,...
0
votes
0answers
36 views

Can Hive selectively reject bad JSON?

We are loading big files full of JSON, one JSON object per line, each LF-delmited line being loaded as a row. If any row fails to match the JSON structure, the entire file is rejected. For example: ...
3
votes
0answers
62 views

Why is PostgreSQL not using composite Index on JSONB Fields?

Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example: SELECT id from table where (j1->>'f1')::int = 1 AND (...
0
votes
1answer
25 views

Combining table under different alias

I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way. Query: SELECT title, ...
0
votes
0answers
36 views

Strict in OPENJSON not work well

I've a table for save sub tasks data. some time we change status of sub tasks, and i need both of them id and status in @JsonStatus parameter. I wrote a stored procedure like below : Declare @...

1
2 3 4 5
10