postgresql json_build_object property LIKE-CodePudding

I have a table with a column named "data" that contains json object of telemetry data.

The telemetry data is recieved from devices, where some of the devices has received a firmware upgrade. After the upgrade some of the properties has received a "namespace" (e.g. ""propertyname"" is now ""dsns:propertyname""), and there is also a few of the properties which is suddenly camelcased (e.g. ""propertyname"" is now ""propertyName"". Neither the meaning of the properties or the number of properties has changed.

When querying the table, I do not want to get the whole "data", as the json is quite large. I only want the properties that are needed.

For now I have simply used a json_build_object such as:

select json_build_object('requestedproperty',"data"-> 'requestedproperty','anotherrequestedproperty',"data"-> 'anotherrequestedproperty') as "data"
from device_data
where id ='f4ddf01fcb6f322f5f118100ea9a81432' 
and timestamp >= '2020-01-01 08:36:59.698' and timestamp <= '2022-02-16 08:36:59.698'
order by timestamp desc

But it does not work for fetching data from after the firmware upgrade for the devices that has received it. It still works for querying data from before the upgrade, and I would like to have only one query for this if possible.

Are there some easy way to "ignore" namespaces and be case insensitive through json_build_object?

I'm obviously no postgresql expert, and would love all the help I can get.

CodePudding user response：

This kind of insanity is exactly why device developers should not ever be allowed anywhere near a keyboard :-)

This is not an easy fix, but so long as you know the variations on the key names, you can use coalesce() to accomplish your goal and update your query with each new release:

json_build_object(
  'requestedproperty', coalesce(
                         data->'requestedproperty',
                         data->'dsns:requestedproperty',
                         data->'requestedProperty'),
  'anotherrequestedproperty', coalesce(data-> 'anotherrequestedproperty',
                                       data-> 'dsns:anotherrequestedproperty',
                                       data-> 'anotherRequestedProperty')
) as "data"

Edit to add: You can also use jsonb_each_text(), treat key-value pairs as rows, and then use lower() and a regex split to doctor key names more generally, but I bet that the kinds of scatterbrains behind the inconsistencies you already see will eventually lead to a misspelled key name someday.