Extract string from json object/array when using DirectQuery mode

[jamienourish]

Hi,

How can I extract a string from this json, when using DirectQuery mode.

I either have 1 object

{"label":"a label","value":"a value"}

and would like to pull out the value.

or an array 

[{"label":"1st label","value":"1st value"},{"label":"2nd label","value":"2nd value"}]

and would like to pull out all values

e.g. "1st value, 2nd value"

Thank you

[v-jiascu-msft]

Hi @jamienourish,

Could you please provide more details?

What's the data source?

Where does the JSON object store?

Do the JSON objects have the same length and the same format?

Best Regards,
Dale

[jamienourish]

Thanks for replying,

Yes the database has a generic column that is a string, and power bi is connected to that using directquery.

in the column I store json that looks like above. 

I just need to extract the value from that string

[v-jiascu-msft · Accepted solution]

Hi @jamienourish,

If all the strings have the same format, please try out the formulas below as calculated columns. The functions we can use are limited due to the limitation of Direct Query. 

FirstValue =
VAR startNum =
    FIND ( """value"":", [json_filed], 1, 9999 ) + 9
VAR chars =
    FIND ( """}", [json_filed], 1, 9999 ) - startNum
RETURN
    MID ( [json_filed], startNum, chars )

SecondValue =
VAR firstPosition =
    FIND ( """value"":", [json_filed], 1, 9999 ) + 9
VAR secondPosition =
    FIND ( """value"":", [json_filed], firstPosition, 9999 ) + 9
VAR chars =
    FIND ( """}]", [json_filed], 1, 9999 ) - secondPosition
RETURN
    IF (
        FIND ( "[", [json_filed], 1, 9999 ) <> 9999,
        MID ( [json_filed], secondPosition, chars ),
        BLANK ()
    )

Best Regards,
Dale

[jamienourish]

You have saved me hours, thank you very much