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
1answer
36 views

Convert PostgreSQL table to structured JSON output

I have been having a horrible time dumping this example output into a neatly structured JSON output. Here is some example data CREATE TEMP TABLE isuck (path text[], city text); INSERT INTO isuck(path, ...
0
votes
1answer
60 views

SQL Server select into vs. insert into performance with embedded cross apply openjson

Note: updated 11/15/2021 to address comment feedback. It seems SQL Server 2016 has some odd little corners when it comes to performance. Version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13....
0
votes
1answer
15 views

Producing JSON in Oracle 12c1

I need to produce a JSON document with several properties from a list of Oracle 12c1 production databases, and I was given read-only access in order to generate this data. The problem is these ...
1
vote
0answers
20 views

Storing user defined forms and their data

I'm writing an application that would have a feature similar to Google Forms. In this feature, some end users of the system will have the ability to design custom forms. Those forms will subsequently ...
0
votes
0answers
35 views

Database based on nesting JSON

I currently am storing raw JSON in a table as I cannot figure out how to create a table structure for the data I am using. The JSON example I have is: { "type": "HEADER", "...
0
votes
0answers
38 views

JSON Search index usable with 'LIKE' but not with '='

We are seeing an issue with Oracle 19c and a JSON search index. Given: A table GEO containing a column JSON_DATA with JSON data and an IS_JSON constraint An index created like this: create search ...
0
votes
0answers
37 views

PostgreSQL query on jsonb column is not using indexes

I have the following table and indexes where all those indexes were created to try and find a solution to my issue: create table persistent_events ( "notificationId" ...
0
votes
0answers
16 views

Fastest way to insert in-memory JSON data into Postgres, other than json_populate_record

We ingest a lot of data, and one of the most time-consuming tasks is inserting data that NodeJS has in JSON format into the database. Currently we're running a query like this, with the giant JSON ...
0
votes
0answers
29 views

How to improve efficiency of multiple JSON_EXTRACT SELECT expressions

In MariaDB + InnoDB, I have a table like: CREATE TABLE `stuff` ( ...
-1
votes
1answer
30 views

Why isn't GIN index being used for JSONB exact match on text?

I have this index: CREATE INDEX ginner ON public.documents USING gin (data) And "explaining" this query: select * from public.documents d where data::json->>'name' = 'Barbara Fritsch' ...
0
votes
1answer
31 views

Incorrect migration of an object from Firebase to MongoDB

I am in the process of migrating a database from Firebase to MongoDB and am facing a problem. Here is such an object I have stored in the FB: "logs": { "2021-05-24": { &...
0
votes
3answers
51 views

How can I return multiple columns from an array of ids?

I have a two tables like the following: workshops table +----+--------------+------------+ | id | name | option_ids | +----+--------------+------------+ | 1 | Conversation | {2,3} | +----...
1
vote
1answer
38 views

Updating a Json column that contains a JsonArray using Json_transform() function in Oracle Database 19c Enterprise Edition

I have a table that keeps USER data. Here is my table structure: CREATE TABLE "USERS" ( "ID" NUMBER(16,0) PRIMARY KEY, "USER_NAME" VARCHAR2(85) UNIQUE NOT NULL, ...
0
votes
0answers
66 views

How to query Postgres' JSON column for specific nested keys

I've got this JSON in a Postgres column: { "type":"doc", "content":[ { "type":"paragraph", "content":[ ...
0
votes
0answers
16 views

Multiple tokens per account

In the context of user account management, I'm using Firebase. So for each login, the front-end sends an Auth ID, a FCM token (for sending notifications to the user), and the usual user details. Each ...
0
votes
1answer
53 views

PgSql jsonb_set for all occurance in json array

I have a json like below [ { "rows": [ { "col_class": "col50", "col_sec_id": 1626361165906, ...
0
votes
0answers
108 views

Trying to read a json lines file with OPENROWSET and cursor

I've got a pile of json lines files that I'm trying to process into a set of Sql Server database tables. Can't use OPENJSON() because jsonlines is not valid in total, only line by line. Plus the ...
0
votes
0answers
14 views

Update a field of type jsonb[] - postgres

Dear Awesome community, I have a table as follow: id | keys | typeid | sectorid | commitid | --------+-----------------------------------------------...
1
vote
1answer
55 views

How to construct a FOR JSON that converts a TOP 1 record into a json object (instead of a json array)

I want to use a JSON_QUERY with a SELECT TOP 1 query such that the resultant json has that top 1 record in object form, instead of in table form? For example, the following query (live demo): CREATE ...
0
votes
1answer
117 views

Calculating Average Value of JSONB array in Postgres

I have a column called "value" in my "answers" table. | value | |---------| | [1,2] | | [1] | | [1,2,3] | The type of "value" is "jsonb". I want to get ...
0
votes
1answer
108 views

Insert a FOR JSON result into a table column

I have the a result of a FOR JSON expression, and I'd like to insert it into a table column. How do I do that? Here is one of my unsuccessful trials (note that the SELECT hightrees ... FOR JSON PATH ...
1
vote
1answer
33 views

Create a json based on multiple queries on the same table when join is irrelevant

I need to construct a json based on the results of two different queries on the same table, where a join is irrelevant. Consider the following example (full demo is here): CREATE TABLE Trees ( [Id]...
0
votes
0answers
27 views

How to convert array objects with object ids as one of key value pair to string?

I have the following JSON, How to convert an array of objects with object ids to string in MongoDB using aggregation query? { "_id": ObjectId("60e41eb7335412757cc6cb9a"), "...
0
votes
0answers
52 views

How to turn a complicated graph into Json Graph Format?

So having such normalized Postgres 13/14 graph of item joins: CREATE TABLE items ( item_id serial PRIMARY KEY, title text ); CREATE TABLE joins ( id serial PRIMARY KEY, item_id ...
-1
votes
1answer
206 views

How to filter and get specific keys from JSON objects array

I have a JSONB field containing a value in this structure: [ { "type": "small", "name": "Josh", "greeting": "Hello ...
0
votes
0answers
39 views

Generic update of "normal" columns with data from JSON

I am trying to do a "generic" insert/update of table columns using JSON. There are tons of info how to update a JSON column, but I have found none that updates several normal columns in a ...
3
votes
1answer
365 views

Why 0/false returns true when using JSON_EXTRACT in MySQL

I have a condition to select a JSON document value that is falsy. My rows: 1. {"my_value": false} 2. {"my_value": 0} 3. {"my_value": "0"} 4. {"my_value&...
1
vote
1answer
68 views

Update an nvarchar column with a new json value, without adding quotes to the new value

I've got the following table, where [Data] is of type nvarchar(max) and its values are valid json strings: Id Data 1 { "info" : { "done": true, "chosenOptions": ["...
6
votes
2answers
181 views

Optimize extraction of json data via OPENJSON

I'm attempting to optimize extraction of values obtained from a REST API which returns json values in an array. Here's an minimal, complete, and verifiable example that reflects exactly what I'm doing....
1
vote
1answer
68 views

PostgreSQL - How do I get all the values from a JSONB search on a nested object?

Postgres 12 with the following jsonb field I need to dig into: {"count":894, "limit":100000, "units":"lin", "observations": [ {"date":&...
0
votes
0answers
78 views

CROSSTAB / cannot extract elements from a scalar HELP PLEASE!

I'm kind of lost here and I really need some wisdom from a Data Expert: cf.item ->> 'name' AS "custom_field" they want everything as a column and not create them as a row and cf.item -...
0
votes
1answer
276 views

How to insert a row from JSON in Postgres

I have 2 tables recording company market data. A table recording stock market symbols CREATE TABLE appl.symbols ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, symbol VARCHAR(10) NOT NULL, ...
0
votes
1answer
43 views

Postgres 12 JSONB key selection with array value

I have a column of a database table that is of type JSONB and I'm wanting to get some data from that column. For the most part the column is a flat list of key \ value pairs. Ex: { s_key: 'value', ...
-1
votes
2answers
70 views

Sum properties of an array of objects, conditionally

I have a JSON field where each entry is of the form [ {date: '2021-06-28', value: 1}, {date: '2021-06-27', value: 2}, {date: '2021-06-26', value: 3}, ... ] I'm trying to sum the values ...
0
votes
0answers
93 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
55 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
984 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
115 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
193 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
394 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
30 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
35 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
207 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
33 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
62 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
38 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
26 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
50 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
77 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
1k 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 ...

1
2 3 4 5
12