0
| names                              | 
| -----------------------------------| 
| null                               | 
| null                               |
| [{name:'test'},{name:'test1'}]     | 
| [{name:'test'},{name:'test1'}]     |
| [{name:'test1'},{name:'test2'}]    |

I want to count the no of rows which does not have the value 'test' in the name key.

Here it should give answer as 3 (Row no 1, 2 and 5th row) because all these row do not contain the value 'test'.

Darshan Sachaniya
  • 389
  • 2
  • 17

2 Answers2

1

Use below approach

select count(*)
from your_table
where 0 = ifnull(array_length(regexp_extract_all(names, r"\b(name:'test')")), 0)

you can test it with below data (that resemble whatever you presented in your question)

with your_table as (
  select null names union all
  select null union all
  select "[{name:'test'},{name:'test1'}]" union all
  select "[{name:'test'},{name:'test1'}]" union all
  select "[{name:'test1'},{name:'test2'}]" 
)

with output

enter image description here

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • regexp_extract_all accepting string as a first parameter. I am not able to pass the my_table.names in the first parameter. What does represent the first parameter over here? – Darshan Sachaniya Jun 16 '22 at 17:40
  • as per your sample - `names` is the column name! – Mikhail Berlyant Jun 16 '22 at 17:43
  • It giving me all rows count. Total fo the table. – Darshan Sachaniya Jun 16 '22 at 17:45
  • that cannot be true if you correctly use above presented query. use CTE I just provided to do test first and then make sure you properly use it with your real data! – Mikhail Berlyant Jun 16 '22 at 17:46
  • also, obviously, make sure that sample data you shown in your question is correctly represents your real data as this can potentially be a reason for returning count of all rows in a table :o) – Mikhail Berlyant Jun 16 '22 at 17:48
  • I think it is because of you take the column type as a string and mine is an array. I am trying with following data set. ```with your_table as ( select null names union all select null union all select [('name','test'),('name','test1')] union all select [('name','test'),('name','test1')] union all select [('name','test1'),('name','test2')] )``` – Darshan Sachaniya Jun 16 '22 at 18:44
  • oh, I see. will revisit when have time :o) – Mikhail Berlyant Jun 16 '22 at 18:48
  • Anyway I found solution by modifying one of your past answer. Thanks a lot for the help! Here is your answer - https://stackoverflow.com/a/42990125/8440056 – Darshan Sachaniya Jun 16 '22 at 18:54
  • great, thank you for confirming :o) – Mikhail Berlyant Jun 16 '22 at 18:54
0

Below approach will work,

with your_table as (
     select null names union all   
     select null union all   
     select [('name','test'),('name','test1')] union all   
     select [('name','test'),('name','test1')] union all   
     select [('name','test1'),('name','test2')]  
)


SELECT COUNT(*) as count FROM your_table
WHERE NOT EXISTS (
  SELECT 1
  FROM UNNEST(your_table.names) AS names
  WHERE names IN (('name','test'))
)
Darshan Sachaniya
  • 389
  • 2
  • 17