59 questions
1
vote
1
answer
81
views
postgresql json arrays combine and group and sort
Please suggest me a solution to a certain problem. I have two tables. One is stored on one server, and the other on another. And I need to combine their data on daily statistics once in a while. The ...
0
votes
1
answer
32
views
Use JSON_ARRAYAGG to create list of values from two output rows
I'm trying to use JSON_ARRAYAGG to create a nested JSON list from two/more output rows. How to re-write my query to get desired output? I tried adding JSON_ARRAYAGG but the output is erroring "...
0
votes
2
answers
164
views
Oracle 19c how to write a stored procedure with JSON output, I'm having trouble formatting the query to return a specific JSON object
The issue when I don't use AGG type I get a 'ora-01422: exact fetch returns more than requested number of rows using a json_object put into clob so using JSON_ARRAYAGG works I just haven't been able ...
- 55
0
votes
1
answer
288
views
evaluate jsonpath expression for the example
{
"a": [
{
"b": {
"c": [
{
"id": "123"
}
],
"d": {
"e&...
2
votes
2
answers
555
views
JSON_ARRAYAGG returns a truncated list of data
I am trying to create a JSON output but it is displayed truncated.
Table creation and inserts:
create table test(c1 number, c2 varchar2(10), c3 varchar2(100));
begin
for i in 1 .. 1000 loop
...
- 1,102
0
votes
2
answers
509
views
MariaDB nested JSON_ARRAYAGG alternative
I did read that nested JSON_ARRAYAGG wasn't supported but I can't find how to rewrite my query
Data :
CREATE TABLE IF NOT EXISTS `table1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` ...
- 123
0
votes
1
answer
97
views
How does React State with json arrays affect performace
I am learning react and I am building an application that has a json array in a state variable. Since react doesn't seem to re-render the page of a json array state variable with out making a copy of ...
0
votes
1
answer
1k
views
MYSQL Select into JSON_ARRAYAGG
I'm trying to do something I thought would be simple but either I'm loosing it after trying to many different ways and my brain stopped working or this is not 'simple'
I have 2 simple tables:
CREATE ...
- 185
0
votes
1
answer
62
views
How do you find or locate just part of the JSON Key name
I am having a hard time getting the JSON Key name. Because the whole name is not fixed. I would like to get the media_id_ but the problem is the numbers next to it is not fixed/static.The goal I am ...
- 3
0
votes
1
answer
608
views
Append to JSON_ARRAY in an existing JSON in ORACLE
I'm trying to combine arrays of JSON documents in Oracle 19c.
I'd like to append items to an array in an already existing JSON stored in a table. I know this question has been asked in various forms ...
- 71
2
votes
1
answer
443
views
Dynamic Column List in JSON Query in Oracle
I'm looking for a way to create/pass a dynamic column list for use in generating a JSON document.
Creating the following (on Oracle 19c):
CREATE TABLE tbl1 (val1 varchar2(10), val2 varchar2(10), val3 ...
- 71
0
votes
1
answer
51
views
find the JSONObject in JSONArray based on key-value of JSONObject
I have a JSONArray with JSONObjects , i want the find a JSONObject based on condition and update it . Consider my json as below
ManagerArr = [
{
name : "Ram",
employees: ["Shyam&...
- 288
1
vote
4
answers
265
views
Oracle JSON_ARRAYAGG doesn't support DISTINCT keyword
SELECT key1, key2, JSON_ARRAYAGG(foo) foo, JSON_ARRAYAGG(bar) bar FROM (
select 1 as key1, 2 as key2, '1.0' as foo, 'A' as bar from dual
UNION
select 1, 2, '2.0' , 'A' as bar from dual
UNION
select ...
- 51
0
votes
2
answers
2k
views
Replace Backslash when converting to JSON Array in Azure Logic App
I am trying to fetch a JSON Array, however the Logic App Response it itself is appending a backslash to the response.
Output expected :- {
"ABC": [
"000D",
"100D",
"...
- 23
0
votes
1
answer
308
views
PLSQL : for loop json_arrayagg to json
I have an emp table, i.e.
insert into emp(id,name,salary) values (1,'name1',1000);
insert into emp(id,name,salary) values (2,'name2',2000);
insert into emp(id,name,salary) values (3,'name3',3000);
...
0
votes
1
answer
301
views
How to use prefix syntax to retrieve a deeply nested setting [array] from an appsettings.json file
Is there a way to retrieve a specific WriteTo object from the following Serilog json snippet from my appsettings.json file using the prefix syntax?
here is the file section
"Serilog": {
&...
- 5,182
0
votes
1
answer
42
views
Can't figure out a triple joint with group by
I want to get the Creators of Artworks, GROUP BY by their role.
I find a way to have the list of the Creators with their role, for each Artwork.
SELECT
JSON_OBJECT('id', a.IDartwork, 'creaDate', ...
- 13
-1
votes
1
answer
730
views
mysql select performance json_arrayagg vs two queries
in mysql 8.0.23
case 1
select *
from boards b
where b.id = 11
select *
from comments c
where c.board_id = 11
case 2
select b.*
, c.comments
from boards b
left outer join lateral (
select ...
- 1
-1
votes
1
answer
386
views
How to convert TSV data to JSON Object in java
TSV Data :
Required OutPut :
[{
candidates: {
id:"agent_4",
text = "can i get a confirmation code",
count = 2},
{
id:"agent_11",
text = "text_2",
count =3},
}]
...
- 71
0
votes
2
answers
317
views
Use JSON array in SQL query in the repository
In my PostgreSQL table I have a parts column of type json. I want to check if the given argument is contained in my json column.
This query works in the database:
select * from elements we
where parts:...
- 17
2
votes
1
answer
2k
views
Skipping JSON_ARRAYAGG array when one of the value is not available-In Oracle
I am working in JSON_ARRAYAGG in Oracle .I have a scenario that Json array have 2 attributes.
One attribute is hardcode value another one fetches value from outer query .
I want to skip whole ...
- 25
-1
votes
1
answer
546
views
How do I access nested elements inside a json array in python
I want to iterate over the below json array to extract all the referenceValues and the corresponding paymentIDs into one
{
"payments": [{
"paymentID": "xxx",
...
- 21
0
votes
1
answer
98
views
How to parse array values from Json in Jmeter
For the below response I need to fetch the rideId and pass it to the next request in Jmeter.Also ,the API that generates below response should be executed until the eventType is HANDSHAKE.
[{"id&...
0
votes
0
answers
58
views
When converting custom list to string e.g.List.toString in JsonArray then facing performance time issue, do we other way to improve performance time
When converting custom object list to string using toString method it increases performance time
list<CustomObject> list;
JSONArray array = new JSONArray(list.toString());
- 11
1
vote
1
answer
463
views
Conditional check for Json array in Karate framework
Want to validate in Karate framework
For the below Json What I want to validate is,
if "isfilter_regex":0 then "msgtype": "##regex ^[A-Za-z0-9_.]-/*"
or if "...
- 31
2
votes
1
answer
391
views
MySQL convert Grouped JSON selection into a single JSON Object
I have a table
---------+-----------
| type_id | A | B | A |
---------+-----------
| stock | 1 | 2 | 2 |
---------+-----------
I'm trying to run
select stock.type_id, sum(stock.stock)
from stock ...
0
votes
1
answer
1k
views
I want to extract the value from JSON by same id one id using java
So the json look like this
{
"cover":"AddressBook",
"Addresses":[
{
"id":"1",
"NickName":"Rahul",
&...
0
votes
0
answers
116
views
Unable to sort JSON string within MySQL
I have a JSON returned from a SQL which is created using json_arrayagg and JSON_OBJECT.
Have applied sort order based on timestamp of column t_last_updated_at.
The output of sql is stored in ...
- 1,365
0
votes
0
answers
79
views
How To Convert Result Table to JSON in MySQL with Multiple Table Join
I have MySql Query like this:
SELECT product_list.id AS listId,
product_list.class AS listClass,
product.prod_name AS prodName,
product.prod_url AS prodUrl,
product....
1
vote
1
answer
2k
views
JSON_ARRAYAGG return value with "\"
I am using json_arrayagg in Oracle to create a json array from multiple rows in a table. Here are details:
create table classInfo (classId integer, category CLOB);
insert into classInfo(1, '{"...
- 29
0
votes
0
answers
151
views
JSON_ARRAYAGG returns itself as key
Given the following query:
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
"empresa_id", agenda.empresa_id,
"nombreemp", agenda.nombre_empresa,
"personas&...
- 1,387
0
votes
1
answer
1k
views
MYSQL - using array in WHERE IN clause
Having a json array column in a table e.g. ["A1", "A2", "B1"]. I want to reference that array in WHERE IN clause. I could not evaluate the json array to ... WHERE tbl2....
- 15
-3
votes
1
answer
49
views
How To Get Json object inside json object
My code snippet for nested json object:
JsonArray arr = jsonObject.getAsJsonArray("data");
for(int i = 0; i < arr.size(); i++) {
String _Id = arr.get(i).getAsJsonObject().get("...
1
vote
0
answers
75
views
how to convert json to row and column format using oracle
{ "Message ": [ { "PACK_NO": "219069571", "COMPONENT_ITEM": [ { "ITEM": "308187417", "PACK_QTY": 2 } ] }, { "PACK_NO": &...
0
votes
1
answer
220
views
How to return total amount of colors without interfering with JSON_ARRAYAGG and LIMIT in FROM subquery
The product has two colors (Black and White)
I would like to use a LIMIT to not bring all the colors... and the amount of colors would be the total
Example:
LIMIT 1 would return:
colorsName: "[{...
- 136
0
votes
1
answer
2k
views
MariaDb vs MySQL JSON_ARRAYAGG JSON_OBJECT Without Escaping
I have a select query, which works just fine in my localhost MySQL database environment. It should return json object.
When I run the same query on my hosted public server with MariaDB 10.5.15 ...
- 311
0
votes
1
answer
1k
views
T-SQL parse JSON array without property name in SQL Server
Environment
SQL Server 2016
Windows 2016
I'm currently getting information from an API. When I call this API, I get the following JSON text back:
{"request":{"command":"...
- 297
0
votes
0
answers
188
views
SQL json_object producing duplicate values
This is what i need to achieve:
{"id":1,
"work":[
{
"name":"Pied Piper3"
},
{
"name":"Pied Piper"
}
],
"...
0
votes
0
answers
368
views
SQL json_arrayarg with json_object producing duplicate values
This SQL query gives me same values repeatedly:
select json_object('id',basics.resume_id,
'work',JSON_ARRAYAGG(json_object('name', work.name, 'location', work.location,
'description', work....
15
votes
1
answer
12k
views
How to return DISTINCT values in a JSON_ARRAYAGG when using JSON_OBJECT
How to use DISTINCT with JSON_ARRAYAGG?
Let's consider the below query as an example.
SELECT
staff.company,
JSON_ARRAYAGG(
JSON_OBJECT(
'uuid', UuidFromBin(staff.uuid),
...
- 1,039
0
votes
1
answer
259
views
How can we get result as Json array in ORDS?
Is there any possibility to get JSON array instead of JSON object as a result while calling REST API which is built by using Oracle Rest Data Services(ORDS)??
1
vote
1
answer
175
views
merge 3 different json array objects into a single json array based on key values in javascript
I am trying to merge 3 different json array objects as a single object based on key values in the base json object in JavaScript. I haven't tried anything like this before.
Data looks something like ...
- 161
0
votes
1
answer
140
views
how to set Text of total amount of prouduct which got from API?
I want to display total amount of product which I got from API. Here is the code which i implemented. Error shows that onErrorResponse: org.json.JSONException: Value Final:92 of type java.lang.String ...
- 23
2
votes
2
answers
6k
views
JSON decoding removing decimal trailing zeros
I have a JsonArray and need to convert it into Array of objects. Below is the code I am using.
response - json response
ArrayList<TestClass> list = new ArrayList<>();
JsonArray ...
- 37
1
vote
1
answer
253
views
Oracle Cloud DB 19c bug with JSON_ARRAYAGG
I try to use JSON_ARRAYAGG into select and get bug result with the same data
SELECT json_object(
'buy' VALUE JSON_ARRAYAGG(b.buysum),
'total' VALUE JSON_ARRAYAGG(b.totalsum)
)
...
- 881
0
votes
1
answer
1k
views
What is the best way of getting index from huge JSON Array of objects that matches with array of JSON objects
I want to know the best way of getting indexes from huge (array length is nearly 150 000) JSON Array of JSON objects that matches with array of JSON objects.
Currently I'm using for loop to do this ...
- 45
0
votes
1
answer
862
views
MYSQL query to get array list with each row having different children
Lets say we have three tables
category_level_one
id
cat_name
1
Physics
2
Chemistry
category_level_two
id
sub_cat_name
cat_l1_id
1
Organic
2
2
Inorganic
2
3
Physical
2
4
Mechanics
1
5
Electricity and ...
- 1,759
0
votes
0
answers
166
views
How to write a query to compare among objects from json object array in Postgresql database
I have a column as jsonb in PostgreSQL which can have the below json array references:
[
{
"endTime": {
"dayOfWeek": "TUESDAY",
"timeOfDay": {
...
0
votes
1
answer
510
views
MariaDB JSON_ARRAYAGG gives wrong result
I have 2 problems in MariaDB 15.1 when using JSON_ARRAYAGG
The brackets [] are omitted
Incorrect wrong result, values are duplicates or omitted
My database is the following:
user:
+----+------+
| id ...
- 156
0
votes
1
answer
325
views
Write a JSON file with cubePosition Data to spawn a 10x10x10 cube
Here I'm trying to write a code that can write and create a JSON File for me with the data I provide like row, column and depth.
For example, I need to spawn a 10 x 10 x 10 cube. And I give this data ...
- 48