How do I query a postgres json array from sql server?

Question

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?

Answer 1

Ah. I guess I never have used the PRINT function. What should the syntax look like to incorporate that function?

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>'')'
PRINT @TSQL   --<------
EXEC (@TSQLB)

Not that you have to use PRINT. SELECT @TSQL works too. The important thing is that you look at the SQL you have generated.

Answer 2

SELECT * FROM OPENQUERY(LOGDB, 'SELECT mac_address,heartbeats[1]->>'group_id' as group_id FROM hubs where location_key = 290 limit 10')

Well, does that look correct to you? You have these tokens: <string-literal> <Identifier> <string-literal>. But OPENQUERY only accepts a single string literal for the second argument. So the resulting statement should be:

SELECT * FROM OPENQUERY(LOGDB, 
    'SELECT  mac_address,heartbeats[1]->>''group_id'' as group_id 
    FROM hubs where  location_key = 290 limit 10')

That is, the single quotes around group_id needs to be doubled.

Composing nesting dynamic SQL is difficult and in order to save your mind, you should work it out step by step. You will need to use the function quotestring.

SELECT @postgres = 
   concat('SELECT  mac_address,heartbeats[1]->>', dbo.quotestring(@VARB), ' as group_id') 
PRINT @postgres
    FROM hubs where  location_key = 290 limit 10')
SELECT @openquery = 'SELECT * FROM OPENQUERY(POSTGRES, ' + 
             dbo.quotestring_n(@postgres)
PRINT @postgres
PRINT @openquery
EXEC(@openquery)

Answer 3

But when running this I get the error:

OLE DB provider "MSDASQL" for linked server "LOGDB" returned message "Requested conversion is not supported.".

I would guess the issue here the Postgres query returns a data type which is not fully supported by the MSDASQL provider, that is the OLE DB provider for ODBC.

It may be better to use a OLE DB provider that is targeted for Postgres. Intellisoft has such a product. Then you get at least one less layer than can produce confusing errors. But to be honest, I would not be surprised that you would get an error on the SQL Server side instead, as the same issue can happen here.

So in the end you may have no choice to cast the column to an easy consumable data type in the Postgres query.

I was not able to get the code snippet you provided to work and I couldn't figure out why the added complexity is needed.

If you are comfortable with counting - how many is it? eight? - single quotes, you don't need. However, I guard my sanity, so I need a more structured approach.