jsonsql-serversql-server-json

T-SQL to retrieve part of data such as array using FOR JSON PATH

I am trying to retrieve the data from our SQL server in a specific way, basically I have a column and it has a delimiter ',' and need to convert like an array using FOR JSON PATH

I have tried using FOR XML PATH and FOR JSON PATH, but I don't figure out how to overcome this query.

Besides that, I have other ways to resolve, mapping using Entity Framework + LINQ, I needed to fix or understand if it is possible in SQL SERVER.

it is my simple data without FOR JSON:

enter image description here

and this is what I am doing:

select * from #templateData FOR JSON PATH

-- result
[
    {
        "id": 1,
        "name": "Full Stack Engineer",
        "description": ".net developer with 20 year of experience,Scrum master,Hold bachelor degree "
    },
    {
        "id": 2,
        "name": "Web Developer",
        "description": "web dev"
    },
    {
        "id": 3,
        "name": "Full Stack Engineer",
        "description": "tested"
    }
]

an this is what I am expecting (see the column description)

[
    {
        "id": 1,
        "name": "Full Stack Engineer",
        "description": [
                         {".net developer with 20 year of experience"},
                         {"Scrum master"},
                         {"Hold a bachelor degree"},
                       ],
    },
    {
        "id": 2,
        "name": "Web Developer",
        "description": "web dev"
    },
    {
        "id": 3,
        "name": "Full Stack Engineer",
        "description": "tested"
    }
]

I have tried using STRING_SPLIT along with FOR JSON PATH using CROSS_APPLY however, without success.

Solution

  • Sorry I posted an incorrect comment before and removed it. I think you want something like this.

    [EDIT]: Now it uses the framework splitter.

    declare @json           nvarchar(max)=N'[
    {
        "id": 1,
        "name": "Full Stack Engineer",
        "description": ".net developer with 20 year of experience,Scrum master,Hold bachelor degree "
    },
    {
        "id": 2,
        "name": "Web Developer",
        "description": "web dev"
    },
    {
        "id": 3,
        "name": "Full Stack Engineer",
        "description": "tested"
    }
]'

;with j_cte(id, [name], [description]) as (
    select * from openjson(@json) with (id               int,
                                        [name]           nvarchar(200),
                                        [description]    nvarchar(4000)))
select id, [name],
    (select [value] from string_split(j.[description], ',') for json path) [description]
from j_cte j for json path;

    Which produces output:

    [
  {
    "id": 1,
    "name": "Full Stack Engineer",
    "description": [
      {
        "Item": ".net developer with 20 year of experience"
      },
      {
        "Item": "Scrum master"
      },
      {
        "Item": "Hold bachelor degree "
      }
    ]
  },
  {
    "id": 2,
    "name": "Web Developer",
    "description": [
      {
        "Item": "web dev"
      }
    ]
  },
  {
    "id": 3,
    "name": "Full Stack Engineer",
    "description": [
      {
        "Item": "tested"
      }
    ]
  }
]