I have this json strings And know i want the following output I wanted to do this with a sql query. If you know how to do this, thanks in advance! Answer If you’re not using MySQL 8, this is a bit more complicated. First you have to find a path to a name element that has the value phone
Tag: json
Migrating data from old table to new table Postgres with extra column
Table Structure: Old Table Structure: New Table Structure: Query: INSERT INTO hotel (id, name, hotel_type, active, parent_hotel_id) SELECT id, name, hotel_type, active, parent_hotel_id FROM …
Postgres get multiple rows into a single json object
I have a users table with columns like id, name, email, etc. I want to retrieve information of some users in the following format in a single json object: Wherein the users unique id is the key and a json containing his information is its corresponding value. I am able to get this information in two separate …
SQL request to Clickhouse: select if value exist in JSON array
I’ve got a following table: How can I do ‘select Status where Name XXX represented at least in one Array[i].Name’? Answer
How to write a SQL query which uses the JSON format of table a_json to populate table b with its respective values in postgresql
I have a file named data.json. The structure is like this: { “PrimKey1”: { “layout”: “normal”, “col1”: “PrimKey1”, “__colll2__”: “sometext”, “col3”: 9, “OTHERCOLUMN”:”dontneedthis”, “&…
How do I get this JSON output?
I need to create the following JSON from SQL server 2016. { “recipientList”: [ { “name”: “1”, “recipientType”: “User” }, { “name”: “2”, …
Select row by user_id and any row which share that entry’s event_id
I have a table which includes the following format: _______________________________ | event_id | user_id | username | |——————————-| | 30 | 1 | user1 | | 30 | …
Use dynamically generated value to check whether a key exists in postgres json
I want to check for keys in my json datatype, where the key is the number of a week. Basically, I use this function to get the number of the next week: extract(week FROM current_date) + 1 Now I want to use the returned value, to check whether this key exists in my object and if not return an empty
Removing backslashes when using Oracle’s JSON_ARRAY function?
How do you get rid of the back slashes, but still keep the double quotes when using json_array? TABLE A: Wrong code: Wrong output: Desired output: And yes, I know you can just concatenate like so “select ‘[‘|| etc. without using the json_array, but I need to have that in the code. Is there a…
Select Unique value from a JSON Array – PostgreSQL JSON column
I have the following JSON document stored in a PostgreSQL JSON column: I am trying to retrieve the price for specific customer_ID I am using this query to get the price for Customer_ID=1 The problem is that i am getting the same results for Customer_ID=1 and for Customer_ID=10 I am basically getting both elem…