Get values from JSON - SQL Server

Question

I have the following JSON:

[
  {
    "email_id": 1598819368,
    "email": "test01@abc.net"
  },
  {
    "email_id": 1598819369,
    "email": "test02@abc.net"
  },
  {
    "email_id": 1598819370,
    "email": "test03@abc.net"
  },
  {
    "email_id": 1598819371,
    "email": "test04@abc.net"
  }
]

How do I return a list of just the email addresses using SQLServer 2017?

-------------------
   emailaddress   
-------------------
  test01@abc.net
  test02@abc.net
  test03@abc.net
  test04@abc.net

I've been trying to use JSON_VALUE or JSON_QUERY but I either only get one email address or NULLs.

Thank you!

score 2 · Accepted Answer · answered Aug 31 '20 at 15:43

This should do it

declare @json         nvarchar(max)=N'[
  {
    "email_id": 1598819368,
    "email": "test01@abc.net"
  },
  {
    "email_id": 1598819369,
    "email": "test02@abc.net"
  },
  {
    "email_id": 1598819370,
    "email": "test03@abc.net"
  },
  {
    "email_id": 1598819371,
    "email": "test04@abc.net"
  }
]'

select email from openjson(@json) with (email_id            bigint,
                                        email               nvarchar(200));

Results

email
test01@abc.net
test02@abc.net
test03@abc.net
test04@abc.net

Get values from JSON - SQL Server

1 Answers1