CODEWITHSUNDEEP
sqlgoogle-bigquery
Tajs
Tajs

Reputation: 621

Access key, value in a JSON object within an array with SQL

I'm a bit stuck with accessing a value in an object contained in a JSON array. I've tried UNNEST() to no avail. Adding OFFSET or ORDINAL did not work. It threw Array index 0 is out of bounds (overflow)

Here is the query:

SELECT d.dealid, associations.associatedvids FROM hs.deals as d 
WHERE associations.associatedvids is not null

enter image description here

Thank you

Upvotes: 0

Views: 459

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

An easier alternative: SAFE

SELECT [][OFFSET(0)]
# Array index 0 is out of bounds (overflow)


SELECT [][SAFE_OFFSET(0)]
# null

Upvotes: 0

Tajs
Tajs

Reputation: 621

The issue was that some rows included an empty array []. I've added WHERE condition to remove such rows:

WHERE ARRAY_LENGTH(d.associations.associatedvids) >= 1

Upvotes: 3

Related Questions