Take json value in sql without JSON_VALUE

Question

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

Iłya Bursov · Accepted Answer · 2018-02-23 02:58:05Z

2

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

answered Feb 23, 2018 at 2:58

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

Take json value in sql without JSON_VALUE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related