Question 408027 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Parsing a table that has a JSON column with multiple rows per field

I have a table similar to this, where I need to pull the value out of each JSON field that has an id equal to 2. I’ve tried multiple examples, but cannot manage to wrap my head around how to manipulate json_tuples / lateral views / explodes into my scenario.

value1,value2,value3,"[{""id"":1,""value"":""x""},{"id"":2,""value"":""y""}, {"id"":3,""value"":""blah""}]"
value4,value5,value6,"[{""id"":1,""value"":""a""},{"id"":2,""value"":""b""}, {"id"":3,""value"":""blahblah""}]"
col1 col2 col3 id2value
value1 value2 value3 y
value4 value5 value6 b

Examples Ive tried to manipulate:
How to extract selected values from json string in Hive

  • I don’t know how to check for a value within the sub-row

Hive Sql Query To get Json Object from Json Array
-dont know how to combine this with selecting the normal columns

Tags:

2

Answers


  1. 0

    Your example is a little unclear, so I’m making some guesses and assumptions (the value* strings are in separate columns, your json string is stored in an array column,… And your quotes in your json array are kind of a mess, that’s not valid json.)

    You can use lateral view on your json array (technically an array of structs), and then you can filter on that.

    select
col1,
col2,
col3,
exp.id,
exp.value
from
<your table>
lateral view inline(<array column>) exp as id,value
where
exp.id = 1

    EDIT:
    If your json is just stored as a string array(of valid json), you have to go through some extra hoops. You convert it to an array with your regex and explode. Then you can pull the individual elements out using json_tuple. If there’s a better/less ugly way to do this, I don’t know it.

    select
col1,
col2,
col3,
t2.*,
jsonstring,
t.col


from
<your table>
--first strip the [] off so hive can see this as an array
LATERAL VIEW explode(
  split(
    regexp_replace(yourtable.jsonstring,'^\[|\]$',''), '(?<=\}),(?=\{)'   
     )
) t as col
--and then use json_tuple to get the elements from the array
lateral view json_tuple(t.col,'id','value') t2 as id,value
where
t2.id = 2
    Login or Signup to reply.
  2. 0

    I’ve Declared a table with same values as you said :

    DECLARE @Value1 NVARCHAR(200) = '{"id":1,"values":"x"}' 
DECLARE @Value2 NVARCHAR(200) = '{"id":2,"values":"y"}' 
DECLARE @Value3 NVARCHAR(200) = '{"id":3,"values":"blah"}' 
DECLARE @Value4 NVARCHAR(200) = '{"id":1,"values":"a"}' 
DECLARE @Value5 NVARCHAR(200) = '{"id":2,"values":"b"}' 
DECLARE @Value6 NVARCHAR(200) = '{"id":3,"values":"blahblah"}' 
DECLARE @T_Table TABLE (col1 NVARCHAR(MAX) , col2 NVARCHAR(MAX) , col3 NVARCHAR(MAX) , id2value NVARCHAR(MAX))

INSERT INTO @T_Table VALUES (@Value1,@Value2,@Value3,'y') , (@Value4,@Value5,@Value6,'b')

    and after that I read all data of col1 , col12 , col13 as one JSON string and select the values with id=2

    DECLARE @Json NVARCHAR(MAX) = ( SELECT '[' + STRING_AGG (col1+','+col2+','+col3, ',') +']' FROM @T_Table)
SELECT [values] FROM OPENJSON(  @Json) WITH ([id] NVARCHAR(MAX)  , [values] NVARCHAR(MAX) )
where [id] = 2

    the final result is :

    y

    b

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search