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

Postgres jsonb field: remove an element from an array by the element id

Figure a Postgres 12.5 jsonb field containing the following: [ {id:1, name: 'one'}, {id:2, name: 'two'}, {id:3, name: 'three'} ] How to write the request that will match: "drop the object ...
0
votes
2answers
18 views

MariaDB JSON functions

I am using MariaDB 10.5, I have a JSON field in a table which typical contains something like: [{"Filename":"C:/Users/simon/Documents/file.ext"},{"Soil":"Sand"},...
1
vote
1answer
41 views

Is it possible to remove a JSON array element by value in MySQL?

In MySQL, it's possible to remove an array element by index: SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`; +------------+ | result | +------------+ | ["...
1
vote
1answer
19 views

How to keep array fields in PostgreSQL jsonb

It seems that PostgreSQL (12+) cannot keep the PostgreSQL array type in jsonb objects. If a PostgreSQL array is stored in a jsonb, it seems that it's converted to a jsonb array. For example, given the ...
3
votes
1answer
137 views

How to turn a set of flat trees into a single tree with multiple leaves?

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
0
votes
2answers
44 views

Postgres Case-Insensitive search for Jsonb column tag keys

I have a requirement to obtain the rows which match given tags key and value case-insensitively. Here: Key search should be case insensitive and values may be String or Array of String. Right Now, I ...
0
votes
1answer
26 views

How to get json data along with select columns in Sql

I'm trying to get the table data as json along with columns on the same table which is joining another table. COUNTRY COUNTRY_ID |COUNTRY_CD 1 |NL 2 |FR 3 |PL ...
2
votes
0answers
31 views

Is it worth using a JSON column type, even if it's only used for simple serialized strings or integers?

I have a table with an unidexed value column of VARCHAR(1024), and I've used my ORM to automatically convert it to and from JSON. The entire reason for this is I want it to be able to store any data ...
0
votes
2answers
72 views

How to quote arrays in postgres copy statement to inser into JSON field

I have some data that looks like this: id,bo,c,url 1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]" 2, "more text", 83, "[{'id': 'W3nAl', '...
0
votes
1answer
30 views

Insert JSONL with two columns of comma separated fields into separate rows in mysql

I have json datas in the format as shown below. **the number of objects under 'images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS...POOL, it may only have ROOMS. &...
0
votes
1answer
29 views

Join two Json arrays to one with key and value

I have two jsonb columns(keys,values). Eg: keys colum value = ["key1","key2","key3","key4"] values column = ["val1","val2","val3",&...
1
vote
1answer
34 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
0
votes
0answers
9 views

How to list all JSON properties in an Azure Cosmos DB?

I will be using SQL queries to get data out of a CosmosDB database. I want to see what nested JSON properties are available to query. Is there a way to do this in the Azure portal, or via a SQL query? ...
2
votes
1answer
45 views

Two queries, one JSON result

I have got two queries, but I just want one result: 1 Query, give me all the data of the column SELECT * FROM dbo.Notificacion FOR JSON PATH, ROOT('notification') 2 Query give me all the column ...
0
votes
1answer
16 views

Amazon Athena database — is there a way to generate an Data Schema for Amazon ML in an automated way

Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way (e.g. a SQL command that would generate the Amazon-format) Please see: https://docs.aws.amazon.com/...
2
votes
2answers
96 views

Why does this throw a JSON parsing error and why can't I trap it?

Consider the below function which returns a portion of a well-formatted JSON object. It will return "Bad JSON" when the JSON is not well-formatted. Importantly, it will not error when the ...
2
votes
1answer
164 views

Postgresql: Modify one value inside of a nested json column

I have a json column called "Parameters" in a "Assets" table. The jsons are nested and have the following structure: { "params1": {"k": 1.1, "efficiency&...
0
votes
1answer
23 views

How to convert strings extracted from JSON to a numeric type for aggregates?

I am struggling to extract data from this JSON object in PostgreSQL: "temperature": { "boiler_temp": { "values": [ "1", "2", "3&...
2
votes
1answer
87 views

How to select sub-object with given keys from JSONB?

How to extract / get / select a "sub object" from a json(b) object in Postgres? There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
0
votes
0answers
25 views

Index on json list

I have an attribute (ids) defined as jsonb in Postgres that contains a list of values: CREATE TABLE test.id_data2 ( key bigint NOT NULL, ids jsonb, CONSTRAINT tag_data2_pk PRIMARY KEY (...
0
votes
0answers
20 views

Most efficient method to import bulk JSON data from different sources in postgresql?

I need to import data from thousands of URLs, here is an example of the data: [{"date":"20201006T120000Z","uri":"secret","val":"1765.756"},{...
1
vote
2answers
38 views

Zip array with values from JSON

Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {&...
0
votes
0answers
18 views

Merging two two nested JSONs in Postgresql

I have two jsons as follows JSON 1 - { "2021-03-11": [ { "14:30:00": 3000 } ], "2021-03-14": [ { "14:30:00": 130 } ] } JSON 2 - ...
1
vote
2answers
36 views

Postgres 11 JSONB - How to get all distinct values of a field across all elements

I have a table called Shipments: CREATE TABLE shipments ( shipmentId numeric, metadata jsonb ); Inside the metaData column there can exist an array of JSON objects named stops which ...
1
vote
1answer
248 views

How to get a JSON array without JSON_ARRAYAGG in MySQL

I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT ...
0
votes
0answers
55 views

How can I set a JSON value to a boolean in SQLite?

I know that SQLite doesn't have a boolean data type, but for a JSON column I'd like to set the value to a real boolean instead of a 0/1 faux-boolean, but SQLite always seems to set it to an int. ...
0
votes
1answer
123 views

Postgres Table toast size is too large [closed]

I want to store some information as JSON in the postgres table. Our application is using a platform. When they started business we store all the data in our db. But now as the application grows we ...
-1
votes
2answers
68 views

Get distinct values for all columns for all tables from specific schema

I would like to get all distinct values for all columns in each table of a specific schema. First, I summarised all my tables/columns in a new_table (myschema.dico) CREATE TABLE myschema.dico AS ( ...
0
votes
1answer
34 views

postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Lets say that tableA has column columnA with jsonb data in it like the following: { "A": 5, "B": 15, "C": 20 } and tableB has columnA with jsonb data in it like ...
0
votes
1answer
27 views

Add jsonb key/value pair with uuid

I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it. What I am trying to add is a key id with a uuid value This is how my data looks right now: { ...
0
votes
0answers
47 views

Oracle 17410(connection reset) when using count(*) on cross join cte with json_table

Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details. Illustrating artificial query is: with data as ( select 5 as some_value from ...
1
vote
0answers
10 views

Editing GeoJSON parsing code to parse JSON code instead in SQL

Apologies if this is an easy question to some, I have spent hours trying to figure this out and it has me stumped. So, I know GeoJSON takes the following format: { "type": "...
0
votes
1answer
53 views

JSON in MySQL : get data from an object within a range of keys

What I have: A mysql table, say table1 table1 contains two columns viz. id and data id is int but the twist is data is JSON type and its keys are comparable the table1 contains only one row (for the ...
1
vote
1answer
729 views

Is the size limit of a json field before or after compression in Postgres?

The size limit of a json field is 1GB (source: this StackOverflow answer): json is the same as a text datatype but with JSON validation. The text datatype's maximum size is 1GB. I ran some ...
0
votes
0answers
16 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
54 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
64 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
30 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
24 views

Read specific fields from Postgres jsonb

I have data like {"name": "a", "scope": "1", "items": [{"code": "x", "description": "xd"}, {"code": &...
1
vote
0answers
145 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
68 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
25 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
29 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
30 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
157 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
31 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
51 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
167 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
651 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
249 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 | ...

1
2 3 4 5
11