CODEWITHSUNDEEP
sqljsonsql-serverstring
Lemons
Lemons

Reputation: 107

Take json value in sql without JSON_VALUE

i want to take value (In SQL Server) in json object without JSON_VALUE

The json value :

{{
  "Url": "****",
  "Token": "",
  "Data": {
    "role_id": 1001,
    "data": {
      "stringvalue": [
        {
          "minage": "21"
        },
        {
          "maxage": "55"
        },
        {
          "primary_identity_file": "data:image/jpeg;base64,/9j/4AAQSkZJRgABAgAAAQABAAD/7QCcUGhvdG9zaG9wIDMuMAA4QklNBAQAAAAAAIAcAmcAFHpFZEQyY1ZzbGVyRzNrcF8yTjhHHAIoAGJGQk1EMDEwMDBhYzAwMzAwMDAwMjMxMDAwMDQxNzQwMDAwYjQ3YjAwMDBhMTgyMDAwMDliY2EwMDAwMjkyMDAxMDBmODJhMDEwMGFiMzQwMTAwMDgzZTAxMDBjZGM0MDEwMP/iAhxJQ0NfUFJPRklMRQABAQAAAgxsY21zAhAAAG1udHJSR0IgWFlaIA"
        }
      ]
    }
  }
}}

what i trying to do is to take "primary_identity_file" value

the result should be :

data:image/jpeg;base64,/9j/4AAQSkZJRgABAgAAAQABAAD/7QCcUGhvdG9zaG9wIDMuMAA4QklNBAQAAAAAAIAcAmcAFHpFZEQyY1ZzbGVyRzNrcF8yTjhHHAIoAGJGQk1EMDEwMDBhYzAwMzAwMDAwMjMxMDAwMDQxNzQwMDAwYjQ3YjAwMDBhMTgyMDAwMDliY2EwMDAwMjkyMDAxMDBmODJhMDEwMGFiMzQwMTAwMDgzZTAxMDBjZGM0MDEwMP/iAhxJQ0NfUFJPRklMRQABAQAAAgxsY21zAhAAAG1udHJSR0IgWFlaIA

** NOTE primary_identity_file value is more than 10K character

Upvotes: 2

Views: 927

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24146

I'd try something like that:

select substring(vlv3, 1, charindex('"', vlv3)-1) as vlv4
from (
    select substring(vlv2, charindex('"', vlv2)+1, len(vlv2)) as vlv3
    from (
        select substring(vlv, charindex('"primary_identity_file"', vlv)+23, len(vlv)) as vlv2
        from test
    ) as test2
) as test3

You can rewrite in more readable way as stored procedure

Sample fiddle http://sqlfiddle.com/#!18/a122b/7

Upvotes: 2

Related Questions