I'm trying to select a key from my db and set its value in a json column with postgres keys are finishing with "_alert".
我正在尝试从我的数据库中选择一个密钥，并在json列中设置其值，postgres密钥以“_alert”结束。
So in my bd I have a column named data as a json and i just want the keys finishing with "_alert" like "ram_alert", "temperatures_alert", "disk_alert", "cpu_alert".
所以在我的bd中，我有一个名为data的列作为json，我只想要键入“_alert”，如“ram_alert”，“Temperatation_alert”，“disk_alert”，“cpu_alert”。
So I need to get the key and the value to compare with the data I have in my backend app to validate if I need to update the value or dont.
因此，我需要获取密钥和值以与我在后端应用程序中的数据进行比较，以验证是否需要更新值或不要。
How to do this?
这个怎么做？
I get all the keys doing select json_object_keys(data) from devices but how to get the key/value pair.. is there a way to use the "like" expression here?
我从设备中选择json_object_keys（数据），但是如何获取键/值对，所有键都可以使用这里的“喜欢”表达式吗？
1 个解决方案
#1
0
First off, note that your current query will only work if you have one tuple in your 'devices' table. Try inserting another row and you'll get:
首先，请注意，只有在“设备”表中有一个元组时，您当前的查询才有效。尝试插入另一行，你会得到：
ERROR: cannot call json_object_keys on an array
If you're certain that you're only ever going to have ONE result from this table, then the following query should give you what you want:
如果你确定你只会从这个表中得到一个结果，那么下面的查询应该给你你想要的：
SELECT key,value FROM devices,json_each(devices.data) where key ~ '_alert$';
I'd still throw something like "LIMIT 1" onto your query to be safe.
我仍然会在你的查询中抛出类似“LIMIT 1”的东西以确保安全。
#1
0
First off, note that your current query will only work if you have one tuple in your 'devices' table. Try inserting another row and you'll get:
首先，请注意，只有在“设备”表中有一个元组时，您当前的查询才有效。尝试插入另一行，你会得到：
ERROR: cannot call json_object_keys on an array
If you're certain that you're only ever going to have ONE result from this table, then the following query should give you what you want:
如果你确定你只会从这个表中得到一个结果，那么下面的查询应该给你你想要的：
SELECT key,value FROM devices,json_each(devices.data) where key ~ '_alert$';
I'd still throw something like "LIMIT 1" onto your query to be safe.
我仍然会在你的查询中抛出类似“LIMIT 1”的东西以确保安全。