How to call Json in sql query

Question

I want to display the parameter ST and NextTime from table @json. The parameters id and Timestamp appear normally. I try the following but does not show any effect.Any possible answers?

My Json

{
    "PCol": [{
        "Id": 15,
        "TimeStamp": "2018-02-1",
        "Val": {
            "States": [{
                "Numbers": {
                    "Number": [5, 8]
                },
                "CS": {
                    "ST": "25"
                },
                "Changes": [{
                    "NextTime": 1
                }]
            }]
        }
    }]
}

My Sql Query

SELECT * FROM OPENJSON((select * from @json),N'$.PCol')
WITH (   
    [Id]  INT  N'$.Id   ',
    [TimeStamp]  NVARCHAR(MAX) N'$.TimeStamp',
    **[ST]  INT N'$.Val.States.CS.ST'**

)

Thanks

Answer 1

Not going to lie, my OPENJSON knowledge is poor (so this might be able to be more succinct), however, this works:

DECLARE @JSON nvarchar(MAX) = 
N' {
    "PCol": [{
        "Id": 15,
        "TimeStamp": "2018-02-1",
        "Val": {
            "States": [{
                "Numbers": {
                    "Number": [5, 8]
                },
                "CS": {
                    "ST": "25"
                }
            }]
        }
    }]
} ';

SELECT P.Id,
       P.TimeStamp,
       V.ST
FROM OPENJSON(@JSON,N'$.PCol') WITH ([Id]  INT  N'$.Id ',
                                     [TimeStamp]  NVARCHAR(MAX) N'$.TimeStamp',
                                     [Vals] nvarchar(MAX) N'$.Val' AS JSON) P
     CROSS APPLY OPENJSON(P.Vals,N'$.States') WITH (ST int N'$.CS.ST') V

For the latest JSON, this works:

SELECT P.Id,
       P.TimeStamp,
       V.ST,
       C.NextTime
FROM OPENJSON(@JSON,N'$.PCol') WITH ([Id]  INT  N'$.Id ',
                                     [TimeStamp]  NVARCHAR(MAX) N'$.TimeStamp',
                                     [Vals] nvarchar(MAX) N'$.Val' AS JSON) P
     CROSS APPLY OPENJSON(P.Vals,N'$.States') WITH (ST int N'$.CS.ST',
                                                    [Changes] nvarchar(MAX) N'$.Changes' AS JSON) V
     CROSS APPLY OPENJSON(V.[Changes]) WITH (NextTime int '$.NextTime') C