SQL Syntax to Pull Array fields from JSON

melissa4 — Fri, 10 Dec 2021 21:51:44 GMT

I'm trying to pull some fields from JSON. Some of these fields can have multiple values. The SQL to pull them works for all the fields except one.

Works:

SQL SELECT
(SELECT
"@Value",
"__FK_age_group"
FROM "age_group" FK "__FK_age_group" ArrayValueAlias "@Value")

"age_group": [

"Adolescents (13-17)",

"Children (2-12)",

"Infants (0-1)",

"Adults (18-64)",

"Senior/Elderly (65+)"

	Qlik SQL Syntax	Results in Qlik	Results in Postman
Works	SQL SELECT (SELECT "@Value", "__FK_age_group" FROM "age_group" FK "__FK_age_group" ArrayValueAlias "@Value")	Adolescents Children	"age": [ "Adolescents", "Children" ]
Doesn't Work	SQL SELECT (SELECT "@Value" AS "@Value_u2", "__FK_social_health" FROM "social_health" FK "__FK_social_health" ArrayValueAlias "@Value_u2" )	No results appear	"social_health": [ null, "Economic\|\|Poverty", "Social\|\|Civic Participation", "Social\|\|Discrimination" ]

Would anyone know why the 2nd example isn't working? Is it because it has a null value? Or because it contains || ?

Thank you.

topic SQL Syntax to Pull Array fields from JSON in Connectivity & Data Prep