0

I'm a little confused about how to parse a JSON string with TSQL. I've been watching Youtube movies for hours, but unfortunately I haven't found a solution yet.

This is the JSONstring:

{"event":"2018","owner_id":"393235","members":{"623118":{"local_score":4,"stars":1,"global_score":0,"name":"thomas ","id":"623118","last_star_ts":"1573807967","completion_day_level":{"1":{"1":{"get_star_ts":"1573807967"}}}},"627679":{"name":"Dickkieee","last_star_ts":0,"completion_day_level":{},"id":"627679","global_score":0,"stars":0,"local_score":0},"623352":{"id":"623352","last_star_ts":0,"completion_day_level":{},"name":"Gerjon ","local_score":0,"stars":0,"global_score":0},"622595":{"local_score":0,"global_score":0,"stars":0,"id":"622595","last_star_ts":0,"completion_day_level":{},"name":"dylan"},"622884":{"local_score":65,"stars":13,"global_score":0,"id":"622884","last_star_ts":"1574197009","completion_day_level":{"1":{"2":{"get_star_ts":"1573765256"},"1":{"get_star_ts":"1573760616"}},"4":{"2":{"get_star_ts":"1573993940"},"1":{"get_star_ts":"1573987479"}},"6":{"1":{"get_star_ts":"1574107994"},"2":{"get_star_ts":"1574147096"}},"3":{"1":{"get_star_ts":"1573826027"},"2":{"get_star_ts":"1573827152"}},"5":{"2":{"get_star_ts":"1574021152"},"1":{"get_star_ts":"1573995867"}},"7":{"1":{"get_star_ts":"1574197009"}},"2":{"2":{"get_star_ts":"1573773372"},"1":{"get_star_ts":"1573768190"}}},"name":"Jeroen "},"393235":{"local_score":336,"stars":50,"global_score":0,"id":"393235","last_star_ts":"1546638507","completion_day_level":{"12":{"2":{"get_star_ts":"1544611601"},"1":{"get_star_ts":"1544607461"}},"2":{"2":{"get_star_ts":"1543839487"},"1":{"get_star_ts":"1543838455"}},"20":{"2":{"get_star_ts":"1545940671"},"1":{"get_star_ts":"1545940531"}},"15":{"1":{"get_star_ts":"1545255445"},"2":{"get_star_ts":"1545306775"}},"19":{"1":{"get_star_ts":"1545741466"},"2":{"get_star_ts":"1545915725"}},"18":{"2":{"get_star_ts":"1545605808"},"1":{"get_star_ts":"1545603795"}},"9":{"2":{"get_star_ts":"1544443203"},"1":{"get_star_ts":"1544362827"}},"6":{"2":{"get_star_ts":"1544090831"},"1":{"get_star_ts":"1544090372"}},"13":{"1":{"get_star_ts":"1544781604"},"2":{"get_star_ts":"1544959450"}},"5":{"1":{"get_star_ts":"1544007960"},"2":{"get_star_ts":"1544012691"}},"4":{"2":{"get_star_ts":"1543915857"},"1":{"get_star_ts":"1543915751"}},"3":{"2":{"get_star_ts":"1543844810"},"1":{"get_star_ts":"1543844649"}},"1":{"2":{"get_star_ts":"1543828757"},"1":{"get_star_ts":"1543654211"}},"23":{"2":{"get_star_ts":"1546638474"},"1":{"get_star_ts":"1546262133"}},"17":{"2":{"get_star_ts":"1546159136"},"1":{"get_star_ts":"1546159088"}},"25":{"2":{"get_star_ts":"1546638507"},"1":{"get_star_ts":"1546441207"}},"7":{"1":{"get_star_ts":"1544177334"},"2":{"get_star_ts":"1544179024"}},"10":{"2":{"get_star_ts":"1544438249"},"1":{"get_star_ts":"1544438218"}},"8":{"2":{"get_star_ts":"1544273070"},"1":{"get_star_ts":"1544270337"}},"16":{"1":{"get_star_ts":"1545233633"},"2":{"get_star_ts":"1545338402"}},"14":{"2":{"get_star_ts":"1545028942"},"1":{"get_star_ts":"1544986949"}},"11":{"2":{"get_star_ts":"1544520723"},"1":{"get_star_ts":"1544517871"}},"22":{"1":{"get_star_ts":"1546213058"},"2":{"get_star_ts":"1546590986"}},"24":{"2":{"get_star_ts":"1546439078"},"1":{"get_star_ts":"1546433420"}},"21":{"1":{"get_star_ts":"1545947800"},"2":{"get_star_ts":"1546209802"}}},"name":"Wilfred"},"622705":{"stars":1,"global_score":0,"local_score":6,"last_star_ts":"1573741255","completion_day_level":{"1":{"1":{"get_star_ts":"1573741255"}}},"id":"622705","name":"William"}}}

Thanks in advance for the help

Improve this question
2
  • 3
    You forgot to ask a question.
    – sticky bit
    Commented Nov 28, 2019 at 13:59
  • 2
    What version of SQL are you working? What does the desirable output look like? What have you already tried?
    – Zohar Peled
    Commented Nov 28, 2019 at 15:00

1 Answer 1

Reset to default
2

Your question does not provide enough information. But you are a new user and I want to help you with this.

Therefore some important hints in advance

  • Always state the actual tool (vendor and version!). You tagged this with [tsql], which points to SQL-Server, but this is not granted. For JSON support you will need SQL-Server v2016+.
  • Try to create a stand-alone, self-running sample. Something we can copy and past to our machines and click "run".
  • Always provide the expected output.
  • And please reduce your sample to the needed minimum. two get_start_ts nodes are enough to understand the 1:n relation.
  • And I hope, that you changed the people's names... Nobody wants to find ones name somewhere in the internet...

And some hints about your JSON:

  • It is a very bad style to use keys for data. If you can change the JSON's creation, you should change something like "1": {"get_star_ts": "1545947800"}, to "aBetterName": {"id":"1","get_star_ts": "1545947800"},.

For a rather all-and-everything-approach you can try this:

DECLARE @json NVARCHAR(MAX)=
N'{"event":"2018","owner_id":"393235","members":{"623118":{"local_score":4,"stars":1,"global_score":0,"name":"name hfkjfdshfsg","id":"623118","last_star_ts":"1573807967","completion_day_level":{"1":{"1":{"get_star_ts":"1573807967"}}}},"627679":{"name":"name hskjkdsafkdf","last_star_ts":0,"completion_day_level":{},"id":"627679","global_score":0,"stars":0,"local_score":0},"623352":{"id":"623352","last_star_ts":0,"completion_day_level":{},"name":"name utoireut","local_score":0,"stars":0,"global_score":0},"622595":{"local_score":0,"global_score":0,"stars":0,"id":"622595","last_star_ts":0,"completion_day_level":{},"name":"dylan"},"622884":{"local_score":65,"stars":13,"global_score":0,"id":"622884","last_star_ts":"1574197009","completion_day_level":{"1":{"2":{"get_star_ts":"1573765256"},"1":{"get_star_ts":"1573760616"}},"4":{"2":{"get_star_ts":"1573993940"},"1":{"get_star_ts":"1573987479"}},"6":{"1":{"get_star_ts":"1574107994"},"2":{"get_star_ts":"1574147096"}},"3":{"1":{"get_star_ts":"1573826027"},"2":{"get_star_ts":"1573827152"}},"5":{"2":{"get_star_ts":"1574021152"},"1":{"get_star_ts":"1573995867"}},"7":{"1":{"get_star_ts":"1574197009"}},"2":{"2":{"get_star_ts":"1573773372"},"1":{"get_star_ts":"1573768190"}}},"name":"name nbnnbvcb"},"393235":{"local_score":336,"stars":50,"global_score":0,"id":"393235","last_star_ts":"1546638507","completion_day_level":{"12":{"2":{"get_star_ts":"1544611601"},"1":{"get_star_ts":"1544607461"}},"2":{"2":{"get_star_ts":"1543839487"},"1":{"get_star_ts":"1543838455"}},"20":{"2":{"get_star_ts":"1545940671"},"1":{"get_star_ts":"1545940531"}},"15":{"1":{"get_star_ts":"1545255445"},"2":{"get_star_ts":"1545306775"}},"19":{"1":{"get_star_ts":"1545741466"},"2":{"get_star_ts":"1545915725"}},"18":{"2":{"get_star_ts":"1545605808"},"1":{"get_star_ts":"1545603795"}},"9":{"2":{"get_star_ts":"1544443203"},"1":{"get_star_ts":"1544362827"}},"6":{"2":{"get_star_ts":"1544090831"},"1":{"get_star_ts":"1544090372"}},"13":{"1":{"get_star_ts":"1544781604"},"2":{"get_star_ts":"1544959450"}},"5":{"1":{"get_star_ts":"1544007960"},"2":{"get_star_ts":"1544012691"}},"4":{"2":{"get_star_ts":"1543915857"},"1":{"get_star_ts":"1543915751"}},"3":{"2":{"get_star_ts":"1543844810"},"1":{"get_star_ts":"1543844649"}},"1":{"2":{"get_star_ts":"1543828757"},"1":{"get_star_ts":"1543654211"}},"23":{"2":{"get_star_ts":"1546638474"},"1":{"get_star_ts":"1546262133"}},"17":{"2":{"get_star_ts":"1546159136"},"1":{"get_star_ts":"1546159088"}},"25":{"2":{"get_star_ts":"1546638507"},"1":{"get_star_ts":"1546441207"}},"7":{"1":{"get_star_ts":"1544177334"},"2":{"get_star_ts":"1544179024"}},"10":{"2":{"get_star_ts":"1544438249"},"1":{"get_star_ts":"1544438218"}},"8":{"2":{"get_star_ts":"1544273070"},"1":{"get_star_ts":"1544270337"}},"16":{"1":{"get_star_ts":"1545233633"},"2":{"get_star_ts":"1545338402"}},"14":{"2":{"get_star_ts":"1545028942"},"1":{"get_star_ts":"1544986949"}},"11":{"2":{"get_star_ts":"1544520723"},"1":{"get_star_ts":"1544517871"}},"22":{"1":{"get_star_ts":"1546213058"},"2":{"get_star_ts":"1546590986"}},"24":{"2":{"get_star_ts":"1546439078"},"1":{"get_star_ts":"1546433420"}},"21":{"1":{"get_star_ts":"1545947800"},"2":{"get_star_ts":"1546209802"}}},"name":"name fsdf"},"622705":{"stars":1,"global_score":0,"local_score":6,"last_star_ts":"1573741255","completion_day_level":{"1":{"1":{"get_star_ts":"1573741255"}}},"id":"622705","name":"name ffdahlf"}}}';

    SELECT A.[event]
          ,A.owner_id
          ,B.[key] AS MemberNumber
          ,C.id
          ,C.local_score
          ,C.global_score
          ,C.stars
          ,C.[name]
          ,C.last_star_ts
          ,D.[key] AS WhatEverNumberThisIs
          ,E.[key] AS AndWhatEverThisNumberIs
          ,JSON_VALUE(E.[value],'$.get_star_ts') AS get_star_ts
    FROM OPENJSON(@json)
    WITH([event] int
        ,owner_id int
        ,members NVARCHAR(MAX) AS JSON) A
    CROSS APPLY OPENJSON(A.members) B
    CROSS APPLY OPENJSON(B.[value]) 
    WITH(local_score int
        ,stars int
        ,global_score int
        ,[name] NVARCHAR(1000)
        ,id int
        ,last_star_ts int
        ,completion_day_level NVARCHAR(MAX) AS JSON ) C
    CROSS APPLY OPENJSON(C.completion_day_level) D
    CROSS APPLY OPENJSON(D.[value]) E;

The idea in short:

We use a cascade of OPENJSON. The nested JSON is returned with AS JSON and can be used in the next OPENJSON.

Improve this answer
1
  • Thanks for your time and solution. It's is great!! :)
    – Dick ter Schiphorst
    Commented Nov 29, 2019 at 11:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.