CODEWITHSUNDEEP
mysqlsql
Zusman
Zusman

Reputation: 666

SQL: combine two columns into one query result

I have a table that represents cameras' configuration.

Say that a camera with primary key x has the following configuration, as a json string which I later use as a dictionary in python: {A:[...], B:[...]}

Now we decided to add an entry for the table with primary key null, which will be set as a project default. There will only be one entry in the table with the primary key as null, say that its configuration column value is {A:[...], C:[...]}.

At the moment I use a very simple query to return the cameras and their configuration:

SELECT pk, configuration FROM static.cameras

Is there a way to return the cameras with their configurations, but fill the missing keys from the project defaults, if available? so for each key in the string, if the camera does not have it, it will fill it up from the default one. Example:

|  pk  |  configuration  |
|  01  |[A:10, B:20, C:1]|
|  02  |[A:200, B:3]     |
|  03  |[D:1000]         |
| NULL |[A:0, D:2]       |

The result of the wanted query will be:

01    [A:10, B:20, C:1, D:2]
02    [A:200, B:3, D:2]
03    [A:0, D:1000]

I know it's easy to do it with python after the query, looping over the results, but is there a way to do it using SQL syntax?

Upvotes: 0

Views: 53

Answers (1)

GMB
GMB

Reputation: 222462

Assuming that configuration is contains valid JSON objects, you can use json_contains_path() for filtering and json_set() to update the value:

update mytable set configuration  = json_set(configuration, '$.D', 2)
where not json_contains_path(configuration, '$.D')

Upvotes: 1

Related Questions