SQL Error: Subquery returned more than 1 value while JSON parsing

Question

My database has json (ExtraData) persisted in it. I want to read the json and extract some file names stored there. In this json there are some image filenames persisted:

    "UploadedPictures": [
        {
          "Type": 0,
          "Filename": "9d349bf8222c4fb6a5fe883dbcd9b7b8.jpeg",
          "Description": "Image 1"
        },
        {
          "Type": 0,
          "Filename": "b2d296a4ee6c49ce80bad90339bdeec1.jpeg",
          "Description": "Image 2"
        },
        {
          "Type": 0,
          "Filename": "d088f43c1a8043cd82533f544dbc7b21.jpeg",
          "Description": "Image 3"
        }
    ],

I want to read these filenames, by I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My SQL query:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) 
    [ID]
    ,[CreatedAt]
    ,[PhoneNumber]
    ,[FirstName]
    ,[LastName]
    ,[HouseNumber]
    ,[City]
    ,[PostalCode]
    ,[Street]
    ,[ExtraData]
    ,(SELECT T.Filename 
      FROM OpenJson(JSON_QUERY([ExtraData], '$.UploadedPictures')) 
      WITH (Filename varchar(200) '$.Filename') T ) AS FileName
FROM 
    [LD].[dbo].[Database]

However when I do a TOP(1) I get the first file name:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) 
    [ID]
    ,[CreatedAt]
    ,[PhoneNumber]
    ,[FirstName]
    ,[LastName]
    ,[HouseNumber]
    ,[City]
    ,[PostalCode]
    ,[Street]
    ,[ExtraData]
    ,(SELECT TOP(1) T.Filename 
      FROM OpenJson(JSON_QUERY([ExtraData], '$.UploadedPictures')) 
      WITH (Filename varchar(200) '$.Filename') T) AS FileName
FROM 
    [LD].[dbo].[Database]

How can I get all the file names?

so what are your desired results? a row per file name? a concatenated string? — Martin Smith
– Martin Smith, Commented Apr 30, 2020 at 14:28
@MartinSmith anything would work.. row or concatenated string say comma separated — Fab C
– Fab C, Commented Apr 30, 2020 at 14:30
Well, before 2017 you can use for xml with stuff to convert rows to a delimited string. — Zohar Peled
– Zohar Peled, Commented Apr 30, 2020 at 15:14

Zhorov · Accepted Answer · 2020-05-02 12:57:21Z

If you want to parse the stored JSON and extract the filenames, you need a different statement:

Table:

CREATE TABLE Data (ExtraData varchar(max))
INSERT INTO Data (ExtraData) VALUES ('{"UploadedPictures": [
        {
          "Type": 0,
          "Filename": "9d349bf8222c4fb6a5fe883dbcd9b7b8.jpeg",
          "Description": "Image 1"
        },
        {
          "Type": 0,
          "Filename": "b2d296a4ee6c49ce80bad90339bdeec1.jpeg",
          "Description": "Image 2"
        },
        {
          "Type": 0,
          "Filename": "d088f43c1a8043cd82533f544dbc7b21.jpeg",
          "Description": "Image 3"
        }
    ]}')

Statement to parse the stored JSON:

SELECT
   -- Include additional columns 
   -- d.*, 
   j.*
FROM Data d
CROSS APPLY OPENJSON(d.ExtraData, '$.UploadedPictures') WITH (
   Filename varchar(200) '$.Filename'
) j

Result:

Filename
9d349bf8222c4fb6a5fe883dbcd9b7b8.jpeg
b2d296a4ee6c49ce80bad90339bdeec1.jpeg
d088f43c1a8043cd82533f544dbc7b21.jpeg

If you want to concatenate the filenames, for SQL Server 2016, you need to use FOR XML PATH:

SELECT 
   --d.*, 
   a.Filenames
FROM Data d
CROSS APPLY (SELECT STUFF(
    (
    SELECT CONCAT(',', Filename)
    FROM OPENJSON(d.ExtraData, '$.UploadedPictures') WITH (
        Filename varchar(200) '$.Filename'
    )
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'), 1, 1, '')
) a (Filenames)

Collectives™ on Stack Overflow

SQL Error: Subquery returned more than 1 value while JSON parsing

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related