How to read a field that is json or a xml object in Crystal Report-CodePudding

I have a field that is json object.

SELECT json_array (json_object ('a' value           1)) j FROM DUAL.

[{"a":1}]

Crystal Report considers that it is a varchar2(4000) (right Click on the field and than click onfield type)

In this example, I would like to give orders like that:

count({command.j}) --How many line has my tab of json. 1

and

{command.j}[1][a] --value of the line 1 for the field a

Is it possible and how do I do that?

A solution that works with xml suits me too.

updated

I've tried ExtractString({YourJsonString}, "[{""a"":", "}]").

But it doesn't work if the json has several fields or array several lines

[{"a":1,"b":2}] -> 1,"b":2},{"a":11,"b":22

[{"a":11,"b":22}] -> 1,"b":2},{"a":11,"b":22 Furthermore. I've said that I don't have nested json. But I would like a function that works in all cases. I'm search for a function that's meant to handle json

CodePudding user response：

Please provide a sample json text and the expected results for it.

CodePudding user response：

Use Split({YourJsonString}, "][") to get an array. The count is simply the size of the array.

To get the value for "a", use:

ExtractString({YourJsonString}, "[{""a"":", "}]")