Skip to content
Advertisement

Tag: json

Update a field in a JSON column in PostgreSQL

I have a work_item table that has the following schema and a document_type table with the following schema: The data column is a json column that has a Type field. This is a sample column data: I need to update data columns whose data_type column value is DocumentModel and Type field values matches a value in the name column of

SQL Server: Update table based on JSON

I am trying to update rows in table based on JSON I have. JSON has the following structure: Table dbo.sensors has same structure + few more columns. To insert such JSON object, not array, into table, I would do it this way: So I have 2 questions: how to iterate over each element in JSON array and update each row

Postgresql, retrieve value for specific key from json array

I have Postgres JSONB array of objects, looking like this : This JSONB is a function argument. What is the most efficient way to retrieve skillLevel for skillId = “1”. I’ve tried to play with jsonb_array_elements but everything I’ve done so far is looking really messy. Answer In Postgres 9.4+ use the function jsonb_array_elements() in a lateral join: You can

MySQL aggregated sum of JSON objects

I have created new table and having details as JSON datatype. I tried to get the aggregated sum of all records. I can able to get the each values but I don’t know how to get the sum using group by …

PostgreSQL json_array_elements with array indexes (keys)

Simple query works fine: But I also want to retrieve array keys somehow so the output would be like: UPD Seems like row_number() is a p̶r̶o̶p̶e̶r̶ solution, but I cannot figure out how to use it further. Lets say i have ‘posts’ table, each post contains an array of related comments in JSON format: The goal is to expand not

Invalid JSON Text – SQL (Java JSON Object)

I’m putting in JSON into an SQL table (type: json) and it claims something is wrong – but I can’t tell what it’s pointing out directly. Current json I’m trying to input: Yes, it is “json within json” but the code I’m writing is kind of dependent on it being that way. I’m not willing to really change that part.

Finding a single element in postgres json array

I have a table that has a Json typed column and I am trying query that table based of values that are contained in that json column. Here is the relevant DDL: Here is what a roles value would look like: I want to be able to perform a query like this: So I’ve tried this: And it is giving

Advertisement