Extract JSON column and normal column with JSON_TABLE

[tjhannes]

Hey,

I want to extract JSON formatted data from a table. The table contains an JSON column as well an ID column which I want to keep for further work. How can I extract the JSON and the "normal" ID column from a table?

CREATE TABLE RAWJOBINFO( ID VARCHAR(1000), 
                                     JSON_COL NVARCHAR(5000)); 
            INSERT INTO RAWJOBINFO VALUES('1', '{"first": "John", "middle": "K", "last": "Doe"}'); 
            INSERT INTO RAWJOBINFO VALUES('2', '{"first": "John2", "middle": "K", "last": "Doe"}'); 
            INSERT INTO RAWJOBINFO VALUES('3','{"first": "John3", "middle": "K", "last": "Doe"}' );

SELECT ID, JOBNO, TITLE 
FROM RAWJOBINFO, JSON_TABLE(RAWJOBINFO.JSON_COL,'lax $' 
                       COLUMNS (JOBNO for ORDINALITY, 
                               TITLE VARCHAR(80) PATH '$.first'
                               ) ) j ORDER BY JOBNO; 

When I run the example it creates the json query for all the IDs. I want it to be only for the corresponding row.

Thank you very much!

[pfefferf]

As you did not describe what exactly you want to extract (the whole JSON or just a specific value) your question is not 100% clear. But I think you want to extract a specific value. For that you can use JSON_VALUE.

[tjhannes]

JSON_VALUE is exactly what I was looking for, thanks a lot!