-2

Okay, so, this is hard to explain but I'll give it a shot. Google didn't help me so please update this question if it can help someone else.

Background

I have a table, Persons, with some columns like [ID], [Name] and [PhoneNumbers]. The table is being filled with data from a third party system, so I cannot alter the way we insert data.

The column [PhoneNumbers] contains a JSON array of numbers, like this:

{"phonenumbers":[]}

I am now trying to write a view against that table, with the goal of having one row for each number.

Question

Can I achive this using T-SQL and it's JSON support? I am using SQL Server 2016.

swaglord mcmuffin'
  • 236
  • 1
  • 11
  • *"Can I achive this using T-SQL and it's JSON support?"* Yes, what was wrong with `OPENJSON`? Your Google search *will* have lead you to its documentation. – Thom A Mar 17 '20 at 15:19
  • I've been trying, but I haven't succeed to understand how I can reach my desired behavior. I have no problem extracting the data, but when it comes to create multiple rows of it - I'm stuck. I might be bad at describing my problem, but pleas, atleast point me in a direction where I can find the answer. A downvote is unecessary before the problem is *correctly* defined. – swaglord mcmuffin' Mar 17 '20 at 15:30

1 Answers1

2
declare @j nvarchar(max) = N'{"phonenumbers":["1a", "2b", "3c", "4", "5", "6", "7", "8", "9", "10x"]}';

select value, *
from openjson(@j, '$.phonenumbers');

declare @t table
(
id int identity,
phonenumbers nvarchar(max)
);

insert into @t(phonenumbers)
values(N'{"phonenumbers":["1a", "2b", "3c", "4d"]}'), (N'{"phonenumbers":["22", "23", "24", "25"]}'), (N'{"phonenumbers":[]}'), (NULL);

select id, j.value, j.[key]+1 as phone_no_ordinal, t.*
from @t as t
outer apply openjson(t.phonenumbers, '$.phonenumbers') as j;
lptr
  • 1
  • 2
  • 6
  • 16