1
$\begingroup$

I have a database (PostgresSQL) which looks like this:

""results"": 
    {""WEBS"": 
        {""WEBS_4"": [{""ip"": ""XXXXX"", ""value"": ""YYYY""}]}}

I would like to access the information of ip.

Then I write the following query in R but doesn't give me any info

dbGetQuery(conexion,"SELECT 
              data->'results'->'WEBS'->'WEBS_4' ->'ip' as test
                          FROM results;")

And when I write this query

dbGetQuery(conexion,"SELECT                   data->'results'->'WEBS'->'WEBS_4' as test
                          FROM results;")

I get a result like this [{""ip"": ""XXXXX"", ""value"": ""YYYY""}], that is not very useful in R.

Some other parts of my database are highly nested, so I would like to access those parts which are with the [] signs.

Thanks a lot!

$\endgroup$
  • $\begingroup$ Welcome to SE; for the next time, please spend a minute to see how to properly format your code (done it for you this time) $\endgroup$ – desertnaut Sep 30 '19 at 15:00
  • $\begingroup$ Thanks about that! $\endgroup$ – MariaKK Sep 30 '19 at 19:23
1
$\begingroup$

Your WEBS_4 is an array with two elements, so there's no ip element in it, which explains why this fails:

dbGetQuery(conexion,"SELECT 
              data->'results'->'WEBS'->'WEBS_4' ->'ip' as test
                          FROM results;")

You need to get the zeroth element of WEBS_4, and then you can get the ip element of that. This works in a simple PostgreSQL test I set up using psql:

SELECT  data->'results'->'WEBS'->'WEBS_4'->0->'ip' as test
                          FROM results;
  test   
---------
 "XXXXX"
(1 row)

So I suspect the relevant R syntax will be (untested):

dbGetQuery(conexion,"SELECT 
              data->'results'->'WEBS'->'WEBS_4'->0->'ip' as test
                          FROM results;")
|improve this answer|||||
$\endgroup$
  • $\begingroup$ Thank you! It worked! I had the feeling that was something easy, but I am starting with SQL so was not so obvious for me. Thank you very much! $\endgroup$ – MariaKK Oct 1 '19 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.