query json values

General
1

looking for a way to query records within json data values
is there a way to return records that have 127 without looping through all records?

[{"14":"8707429904,33814205264"},{"15":"8707430672,33814206352"},{"SC7603000002\\/236717":"9198100304,36533387344"},{"SC9003142557822":"8707431056,33814207056"},{"SC9003142557822":"9201900816,36542877200"},{"SC9003142557822":"9202077968,36543687824"},{"SC9003142557822":"9202146256,36543835472"},{"SC276870":"9202082128,36543696784"},{"127":"8780570576,34354928080"},{"127":"8780608784,34355362896"},{"127":"8780620752,34355414992"},{"23":"8870499984,34989266704","24":"8870499984,34989266768","25":"8870499984,34989266832"},{"23":"8870562192,34989645264","24":"8870562192,34989645328","25":"8870562192,34989645392"},{"SC9003140086614":"9202151952,36543858064"}]
2

Records that have 127 what? As the key?

For that you’d need a loop. Should be relatively fast though… they’re backed by arrays and dictionaries.

3

If postgres, there are operators for that.

4

I was afraid looping would be the answer.

I was previously using a dictionary and using hasKey but someone needs duplicate keys.

There maybe 50k records that may have to loop through 50k records vs 50k records checking hasKey.
Sounds like it will suffer greatly in performance.

Looking for options:

  1. Use in memory db in order to use where clause
  2. Allow max of x duplicates by creating a next dictionary if key exist in the original.

Am I making sense?

Thoughts?

5

Use an in-memory DB.

6

He’d still have to iterate over the JSON to copy the data into memory.

[quote=333333:@Tim Kearns]There maybe 50k records that may have to loop through 50k records vs 50k records checking hasKey.
Sounds like it will suffer greatly in performance.[/quote]
Well, technically you could probably use RegEx to do this… something like:

[code]dim rx as new RegEx
rx.SearchPattern = “(?mi-Us){”“127"”:“”([0-9,]+)“”}"

dim rxOptions as RegExOptions = rx.Options
rxOptions.LineEndType = 4

dim match as RegExMatch = rx.Search( sourceText )
while not match = nil
dim Value as String = rx.subExpressionString(1)
match = rx.Search()
wend
[/code]

*Special thanks to Kem’s RegExRx for that code

7

But he’d only have to do it once, not on every query.