question

CraigFeeck-3442 avatar image
0 Votes"
CraigFeeck-3442 asked ErlandSommarskog answered

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

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?

sql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shouldn't you ask this in a Postgres forum? Here we can tell you how to write queries on SQL Server, but you are running a query on Postgres.

Always when you work with dynamic SQL, include a PRINT, so that you see what you have produced?

0 Votes 0 ·

The reason for doing it here is that I can direct query through a postgres client or even heidiSQL all day, so the issue isn't the postgres DB or even postgres syntax. Where I run into trouble is trying to query from SQLServer so that I can pull the output into an already existing SQLServer database. For that reason I thought that someone in the SQLServer community might have experience doing this sort of thing and could help me jigger my transact-SQL code.

However I suppose I could poke a postgres community as well.

0 Votes 0 ·

So did you use PRINT @sql to verify that the query you sent to Postgres has the expected syntax?

If you show the resulting Postgres query looks like, we can help you to build it. But if we don't know what you want query string you want, it is difficult to assist.

0 Votes 0 ·
Show more comments

ISo I was able to use PRINT to validate the syntax.

Here is the actual PRINT result

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

So, if I take the values inside the inner apostrophes (select...limit 10) and copy that into heidisql to run against the same db, the query works perfectly.

However, when done through openquery in SQL server I get this error:

Msg 102, Level 15, State 1, Line 36
Incorrect syntax near 'group_id'.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered pituach edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered CraigFeeck-3442 commented

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)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I find that if I merely added two more quotes around the @VARB section that the passed syntax matches what you said I needed:

New Code: SELECT @TSQLB = 'SELECT * FROM OPENQUERY(LOGDB,''SELECT mac_address,heartbeats[1]->>''''' + @VARB + ''''' as group_id FROM hubs where location_key = 290 limit 10'')'

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

But when running this I get the error:
OLE DB provider "MSDASQL" for linked server "LOGDB" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 36
Cannot get the current row value of column "[MSDASQL].group_id" from OLE DB provider "MSDASQL" for linked server "LOGDB".

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.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.