SQL and JSON database - R

Question

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 it does not 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.

Answer 1

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;")