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 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.

$\endgroup$
0
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;")
$\endgroup$
0

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.