Get data as rows from array inside JSON column

Question

I have a table which the 'Preference' column stores JSON strings.

a) I would like to get the type from companytype as rows. In my attempt on sqlfiddle I get the five rows but no data in them.

SELECT z.[Type]
FROM FinPreferences p
CROSS APPLY OPENJSON(Preference,'$.companytype.type') WITH (
    [Type] INT
) z
WHERE PreferenceID=1

b) How can I get the results as one string row i.e

1,2,3,4,5

This is the data inside the Preference column

   {
    "companysize":{"min":0,"max":5},
    "distance":{"min":100,"max":200},
    "companytype":{"type":[1,2,3,4,5]},
    "budget":{"min":1000,"max":2000}
    }

Fiddle is here

@ManojNaik there's nothing wrong with OPENJSON. JSON_QUERY won't return a rowset either — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 1, 2018 at 10:03

Panagiotis Kanavos · Accepted Answer · 2018-10-01 10:01:24Z

7

Either remove the WITH clause and use the value field to retrieve the array's values, eg:

SELECT z.value as Type
FROM FinPreferences p
CROSS APPLY OPENJSON(Preference,'$.companytype.type') z
WHERE PreferenceID=1

Or use just $ as the path in the WITH clause :

SELECT z.Type
FROM FinPreferences p
CROSS APPLY OPENJSON(Preference,'$.companytype.type') 
            WITH ( Type int '$') z
WHERE PreferenceID=1

WITH applies to the results returned by OPENJSON. The path $.companytype.type is just an array of integers, it doesn't have any other attributes

answered Oct 1, 2018 at 10:01

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alwaysVBNET Over a year ago

This works. How would I get the results as one result such as 1,2,3,4,5 which is the part B?

alwaysVBNET Over a year ago

Tried this but I get no values: SELECT JSON_VALUE( Preference, '$.companytype.type' ) AS [Type] FROM FinPreferences p WHERE p.PreferenceID=1

Panagiotis Kanavos Over a year ago

@alwaysVBNET that's a different question. JSON_VALUE returns scalar values, eg JSON_VALUE( Preference, '$.companytype.type[1]' ) will return 2. You need JSON_QUERY if you expect to return multiple values, like the contents of an array, eg JSON_QUERY( Preference, '$.companytype.type' ) will return [1,2,3,4,5]

Collectives™ on Stack Overflow

Get data as rows from array inside JSON column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related