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
27 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
56 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
184 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
37 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
40 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
41 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
161 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
516 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
360 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
387 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
284 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
222 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
43 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
79 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
38 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 @...
2
votes
1answer
64 views

In a Postgres function, how to skip expressions if input value is null?

I have a working function. But it only works if I literally supply both the city and country variables. CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text) RETURNS SETOF ...
1
vote
0answers
101 views

Perform SELECT using WHERE IN clause on JSON Object in MySQL

I have a json column in MySQL which contains a list of items for eg. "style": {"style1", "style2", "style3", "style4", "style5"} I want to ...
0
votes
0answers
28 views

Storing raw results in a relational database

I have used a star-schema to build a manufacturing database. I'm receiving results of tests (eg. Temperature: 273 K) and I'm storing them in a fact table (transactions). The problem is, some machines ...
0
votes
0answers
16 views

Postgresql : UNNESTING & NESTING

I have below jsonb in table. '{ "Owner":[ { "Edgeid":4, "Weight":40, "EdgeColor":"Black" }, { ...
0
votes
1answer
344 views

postgresql : **ERROR: path element at position 1 is not an integer:**

I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution. i am trying to swap values of OutgoingVertexid &...
1
vote
0answers
118 views

Should I use Postgres' JSON columns for additional related data of different types and not yet known?

I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys. The tbl_main is the most frequently written to table, as in it receives new ...
0
votes
1answer
594 views

Extract all objects from json array using MariaDB 10.3

How can I extract every object from this json array using MariaDB 10.3? I think the JSON_TABLE function in MySQL does what I need, but I'm currently constrained to MariaDB. SET @json_doc='[ { ...
2
votes
1answer
62 views

Caveats of OUTER JOIN on nested JSON value

I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
0
votes
0answers
59 views

to_json vs ::json in postgres

I was wondering how to address the tsrange problem in postgres, as tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json returns annoying escapes: "[\"2010-01-01 14:30:00\",\"2010-...
1
vote
1answer
313 views

Join table from values nested deeply in a jsonb field

In Postgres 12.3 I am having trouble building the correct SQL query that can join a table from a deeply nested array in a jsonb object with rows from another table. The deeply nested array contains ...
0
votes
0answers
18 views

Mysql result as Json Object

I have the following MySql query: SELECT E.stock_code, JSON_ARRAYAGG( JSON_OBJECT( "stock_code", P.stock_code, "PROM_NO", ...
0
votes
1answer
41 views

How to store long string with netstring format in varchar column?

I am involved in project where we have to store very long string in varchar column (varchar(max)). Due to this case have to questions: This column is one of other 40 columns in table (but only this ...
0
votes
0answers
21 views

How to preform a query as argument in PL/pgSQL function which returns JSON?

I'm trying to perform select by using already created function which returns JSON which aggregates into JSON array, but I'm getting problem in line where I'm performing a SELECT-clause. Tables I have ...
0
votes
1answer
455 views

Postgres group and aggregate(sum) JSONb arrays and non JSONb properties

Version PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values ...
1
vote
1answer
84 views

Update dynamic jsonb array of objects

I am trying to update a jsonb key with a new value, but I cannot update all the keys at once. My json structure is something like this: [{ "type": "button", "content": { "align": "...
3
votes
1answer
298 views

Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column ...
0
votes
0answers
275 views

MySQL/MariaDB extract multiple values from a JSON column

My goal is to extract about 20 fields out of JSON column with 100 fields, some are strings, total length of json text is around 40kb. The only way I found it's possible is to write it like this: ...
0
votes
1answer
46 views

Relational-JSON overlapping with association [closed]

I have a composed class called sensor that is linked to a main class Node such that: class node is linked with another node to make a communication that produce an event class sensor can as well ...
0
votes
0answers
555 views

MariaDB: SQL query to return array of json objects and remove `\` from response

I am creating a sql query for api response in javascript. But i stuck on one part that is creating a array of json objects of same group. I have checked with the json_arrayagg and also ...
1
vote
2answers
56 views

What are the benefits of storing columns in JSON instead of traditional tables?

Are there any benefits in using JSON(B) over traditional table structures? Imagine having a table structure like this: create table table1 ( t_id int, first_name varchar(20), ...
0
votes
1answer
29 views

query an indexed jsonb column

I want to query a jsonb column with the @> operator. This query gives results: select * from json_table1 where p_attribute @> '{"age":"37"}' This query doesn't fail but also doesn't give ...
0
votes
2answers
4k views

Using Python to insert JSON into PostgreSQL [closed]

I have the following table: create table json_table ( p_id int primary key, first_name varchar(20), last_name varchar(20), p_attribute json, quote_content text ) Now I basically ...
0
votes
1answer
64 views

Query problem with array_to_json()

Considering my basic knowledge of PostgreSQL I am unable to find out why my second statement for where clause (record->'details'->>'amount')::numeric(19,2) = 2.99 is not working with current ...
1
vote
0answers
175 views

Postgres Unnest or flatten a nested array inside of JSONB

I've got a table MY_TABLE that has the following columns: User Name Data Purchased Data is of the form : [{ "Id": 1, "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
0
votes
1answer
27 views

How to insert json into a table and have certain columns remain json [closed]

I want to insert data into a table where the first level keys are inserted as values and not as json. Although column "p_attribute" needs to store the nested json. For example, in case of Jane Doe, I ...
0
votes
1answer
38 views

How to import values of a JSON to PostgreSQL?

I have a JSON file gugu.json that looks like this: { "key1": { "value1": "normal", "value2": [ "PogU" ] }, "key2": ... } I want ...
1
vote
2answers
105 views

MySQL 8.0 table with JSON column that uses JSON “merge” operations has 500k rows but analyze table shows 7MM

I have a database table that according to TABLE STATUS has 7MM rows, but when I SELECT COUNT(*) it only has 500k rows. This is a problem because table growth is increasing and we're running low on ...
0
votes
1answer
112 views

MariaDB Maxscale REST API can't show result

hi i have already setup maxadmin on maxscale can access from rest api this is my configuration ..... [CLI_Service] type=service router=cli [CLI_Listener] type=listener service=CLI_Service ...
1
vote
1answer
262 views

Postgres SELECT rows where JSON array contains “most” of values

I have the following table structure: CREATE TABLE items ( id SERIAL PRIMARY KEY, labels json ); INSERT INTO items (labels) VALUES ('{"labels":[{"value":"apple","score":0.95},{"value":"fruit"...
0
votes
1answer
53 views

Return master and master details record(s) in a single row in postgresql

I am trying to formulate a PostgreSQL query where I wanted to return all the payment table data in an array and customer data as a header in a single row. I am writing the following query to get data ...
1
vote
1answer
714 views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as ( ...
1
vote
0answers
18 views

Can I assign an index to an exploded array?

I'm doing a LATERAL EXPLODE on an array in Hive, is there a way of reliably assigning a row number based on the array element? It looks like calling row_number() on the results of the LATERAL EXPLODE ...
0
votes
0answers
32 views

Can we partition or split json file while insert

I have a table with a JSON column, large files coming from the source. The issue is here while flattening data from that file is taking a long time some and sometimes DB server crashing and going to ...

1
2
3 4 5
11