# SQL and JSON database - R

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.

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