0

I have a table with simple & complex entries:

id | formats | ...
1   [array]
2   [array]
...

I can select the rows I want based on some other columns. The [array] is a list of complex entries

formats:
  [{
    format: "blah1"
    hash_key: "hash_key1"
  },
  {
    format: "blah2"
    hash_key: "hash_key2"
  },{
    format: "correct"
    hash_key: "hash_key3"
  },
  ...
  ]

I need to loop through the the list of formats and if format=="correct" select the hash_key. So I will return all of my rows with:

id1, hashkey
id2, hashkey
...

I don't know how this can be done in SQL. This would be easy with a while loop in C++ or Python, but I need to do it in SQL here. I need to do this in Spanner SQL as this might matter. I can try any standard SQL answers.

user3480774
  • 853
  • 3
  • 10
  • 18
  • 2
    Which DBMS product are you using? Postgres? Oracle? –  Jan 11 '21 at 21:33
  • If you're using SQL Server, the [OPENJSON](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15) method is what you're looking for [Here is an example](https://stackoverflow.com/a/47239341/14956277). – D M Jan 11 '21 at 21:34

0 Answers0