I need to obtain the record that is inside a table in which there is a JSON column in which one of its keys matches a given one.
SELECT json_object_keys("Localizacion") LIKE 'prop1'
FROM ejemplo "EstanciaHospitalaria"
Returns if in each record the key matches one or not, if it matches it returns t and if it does not match it returns f .
What I really want to show is all the information that is in the table record, in the code above it returns t .
You can use a EXISTS expression like this:
SELECT *
FROM ejemplo "EstanciaHospitalaria"
WHERE EXISTS (
SELECT NULL
FROM json_object_keys("Localizacion") jkey
WHERE jkey LIKE 'prop1'
)
.. and since you are not comparing anything special with the operator LIKE , in this case you can simply use the operator = and get the same results:
SELECT *
FROM ejemplo "EstanciaHospitalaria"
WHERE EXISTS (
SELECT NULL
FROM json_object_keys("Localizacion") jkey
WHERE jkey = 'prop1'
)
As I understand you want to return the value of the json every time it is fulfilled that the key is equal to a certain value ?. If so, this would be a possible solution:
select js, js_key, case when js_key = 'Localizacion' then js->>'Localizacion' end as Lozalizacion
from (select '{"Id": "1","Localizacion": "Chile"}'::json js, json_object_keys('{"Id": "1","Localizacion": "Chile"}'::json) js_key) a;