As mentioned in the title, i’ve been trying to aggregate json objects to a json array where the id is the same. I’ve got a main table “kurs” that has a unique id and is connected to the table “kompetenz” over a link table. For each “kurs” there can be multiple “kompetenzen”. However after a few hours of trying i
Tag: json
SQL get the value of a nested key in a jsonb field
Let’s suppose I have a table my_table with a field named data, of type jsonb, which thus contains a json data structure. let’s suppose that if I run I get so in pretty formatting, the content of column data is I know that If I want to get directly in a column the value of a key (of “level 1”)
SQL: extract keys and values of a jsonb field as rows of two separate columns
In my postgresql DB I have the following query returning where json_field is of type JSONB. I am looking for a way to query all the keys and all the values from this json field, so that the output would be Is there a way to do it ? generalizing the question after it got the solution answer When I
MySQL – How to use JSON_EXTRACT to get values between two indices
I am trying to extract values between 2 indices in a JSON array using mysql JSON_EXTRACT. This query will smoothly return 20 as result. But if I want to get all the numbers between, say, 1st and 3rd indices, how do I query it? I was expecting something like: which will return 20,30,40. But not working. How do I achieve
Dynamanically add column(s) and insert values into a Sqlite TABLE from json objects
I am using the Python tweepy library to scrap and collect tweet data for a research purpose. Specifically is use tweepy.StreamingClient class to get tweet the from stream. Returned data is json object whose elements depends on the data fields available with retrieved tweet post. So for example, at one time I received json object with few elements like this:
Python SqlAlchemy adding escape characters to json string while inserting in postgresql json column, so queries not working
I am adding JSON string to Postgres jsonb column in python using the sqlalchemy library. Code to create json string is Code to add to postgresql database is: My JSON is printed properly in logs But when it goes to database the json is enclosed in a string and an escape character is added in front of every double quote.
Left join add matched rows in child table to an array in parent row (as JSON format)
I have the following two tables: My goal is to do a (left?) join and get all matching rows in the child table based on the FK in an array on the parent row and not inside the parent row itself on matching column names (see further down on desired output). Examples of values in tables: Parent table: Child table:
Sum of column values inside Json array with group by
I have next Django model. I store JSON data in wh_data. Note: it’s data for one row – 2022-06-06. I need to calculate the sum inside stocks by grouping them by wh and by created_at so that the output is something like this I know how to group by date, but I don’t understand how to proceed with aggregations inside
SQL Group by JSON arrays
So I have a JSON variable (var2), which is an array. I have the following data: I’m trying to write a query that gives back the number of dollars and the machines per group: Example: I’m not used to JSON arrays and it’s kinda tricky for me. I’ve tried many things and I can’t find much documentation with examples for
PostgreSQL – Adding up json values
This is my json column in pgSQL and I need to add up all of the “sum” amounts using pgSQL. So the query should return “50” using the data below. I did find something like this (Get aggregate sum of json array in Postgres NOSQL json data) that kinda works if I had my values in array, but I dont,