postgresql json_build_object property LIKE

Question

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.

Mike Organek · Accepted Answer · 2022-02-19 12:16:09Z

1

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.

answered Feb 19, 2022 at 12:16

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moelbeck Over a year ago

Thanks, Mike, it solved my problem! :)

Collectives™ on Stack Overflow

postgresql json_build_object property LIKE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related