Skip to content

Tag: json

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,

Implement Scan and Value functions in golang

I am trying to store some golang objects in SQL database and have implemented the scanner and value interface as follows: Is there a way that I can pass the parameter to the Value() function by pointers, as I am getting a HugeParam error that the attr passed to the Value() function is too big, when trying to convert the

Performing division with PostgreSQL / json

I have written a simple query against a table in a Postgres database that contains a column “manifest” of type json. Each cell contains a very long value and I am extracting the numerical value for “size”. I need to create a new column (perhaps call it “size in MB”), and perform division against size. Specifically, I need to take

Postgres: Searching all sub-arrays of an array

I have a Postgres table with a json column, named raw_data, that is structured like this: [{“id”:1234, “name”:”John Doe”, “purchases”:12}, {“id”:1234, “name”:”Jane Doe”, “purchases”:11}] The number of sub-arrays can differ. It may be 1 or 20 or any number. I want to perform a query where I can return the entire table row if, say, ‘John Doe’ occurs in the

Extract all records from a JSON column, using JSON type

I have a couple tables (see reproducible code at the bottom): tbl1_have tbl2_have I wish to extract all json columns without providing explicit data type conversion for each columns since in my use case the names and amounts of nested attributes vary. The expected output is the same for both cases: tbl_want with a_i and a_j correctly stored as a

Generate rows from JSON (SDO_GEOMETRY)

I have a multi-part SDO_GEOMETRY that I can represent as JSON (Oracle 21c): As an experiment, I want to find a way to generate rows from the JSON: Each row is a vertex of the geometry. Question: In an SQL query, is there a way to generate rows for each of the JSON vertices? I’m aware that SDO_GEOMETRY has a

Complex multi-level JSON and SQL – what am I doing wrong?

So I am able to query my JSON file fine then I wanted to add another field that is under a few levels and when I try to do this – it doesn’t work. What am I doing wrong? So the following works correctly and gives me back the first level promoter id This does not work – I get