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

Question

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

Thank you

Your first id is not null. Try != '' in addition to the is not null — smoore4
– smoore4, Commented Feb 19, 2020 at 14:55
Can you think of any WHERE condition that will work with []. It seems that != '' still returns rows with []. No matching signature for operator != for argument types: STRUCT<value INT64>, STRING. Supported signatures: ANY != ANY at [2:7] IS NOT NULL does return some rows with []. — Tajs
– Tajs, Commented Feb 19, 2020 at 15:06

Tajs · Accepted Answer · 2020-02-19 15:14:53Z

3

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

answered Feb 19, 2020 at 15:14

Tajs

6518 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Felipe Hoffa · Accepted Answer · 2020-02-20 22:09:56Z

0

An easier alternative: SAFE

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


SELECT [][SAFE_OFFSET(0)]
# null

answered Feb 20, 2020 at 22:09

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related