I am attempting to query a postgres database that includes a json array as one of its columns.
I have managed to connect to the table but have found that direct queries don't work and that I need to use the open query structure.
I have found that I can successfully query the standard data fields, but that the queries don't work for JSON. At present the closest syntax I have been able to come up with is below, where "address" is a text field and "heartbeats" is the json array and group_id is the value I'm trying to select from the JSON array.
DECLARE @TSQLB varchar(8000), @VARB char(20)
SELECT @VARB = 'group_id'
SELECT @TSQLB = 'SELECT * FROM OPENQUERY(<connection name>,''SELECT address,heartbeats[1]->>''''' + @VARB + '''''as group_id FROM <db>.<schema>.<table>'')'
EXEC (@TSQLB)
This query does not error and it returns the address, but instead of values for group_id it returns .NULL. values, which makes me suspect that the value for VARB that is being passed isn't the exact syntax needed to get a valid result.
Any insights on how to structure the query to return actual values?