Using JSON DATA Without OPENJSON And Search by Value

Question

[
    {
        "Level"       : "S1",
        "Major"       : "Teknik Informatika"
    },
    {
        "Level"       : "SMA",
        "Major"       : "IPA"
    },
    {
        "Level"       : "SMP",
        "Major"       : "Umum"
    },
    {
        "Level"       : "SD",
        "Major"       : "Umum"
  }
]

iD  | UserID  | Education
------------------------------------------------------------------------------
1   | B000-1  | [{"Level":"S1","Major":"TI"},{"Level":"SMA","Major":"IPA"}]
2   | B000-2  | [{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3   | B000-3  | [{"Level":"SMA","Major":"IPA"}]
4   | B000-4  | [{"Level":"SD","Major":"Umum"}]

Jika setiap kolom table Education kurang lebih berisi seperti object diatas, bagaimana saya mencari data Education[Level]='SMA' tanpa menggunakan OPENJSON, karena saya menggunakan sql server 2012

Translate : If every column in Education table contains objects like above, how can I find Education[Level]='SMA' without using OPENJSON, because I am using SQL Server 2012

iD  | UserID  | Education
------------------------------------------------------------------------------
1   | B000-1  | [{"Level":"S1","Major":"TI"},{"Level":"SMA","Major":"IPA"}]
2   | B000-2  | [{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3   | B000-3  | [{"Level":"SMA","Major":"IPA"}]

What about brute-force LIKE? WHERE Education LIKE '"Level":"SMA"... Will it work with your data type for Education column? — PM 77-1
– PM 77-1, Commented Dec 22, 2022 at 16:15
If you cannot use LIKE directly, you can CAST the column to varchar. — PM 77-1
– PM 77-1, Commented Dec 22, 2022 at 16:18

Bas · Accepted Answer · 2022-12-22 16:38:01Z

0

A quick search lead me to Consuming JSON Strings in SQL Server

Here the auther shows how 'older' versions of SQL Server can read/write JSON objects/strings. The solution needs to be tailored to your needs, as your JSON structure is different, but it is possible.

Or you can brute force like .. like PM 77-1 suggested above

answered Dec 22, 2022 at 16:38

Bas

356 bronze badges

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

Comments

Yitzhak Khabinsky · Accepted Answer · 2022-12-22 20:58:47Z

Please try the following solution.

We are converting JSON data into attributes based XML. For example, for the first row it will be like follows:

<root>
    <r Level="S1" Major="TI"/>
    <r Level="NotSMA" Major="IPA"/>
</root>

After that XQuery method .exist() is used to search for the @Level parameter.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Education NVARCHAR(MAX));
INSERT @tbl (Education) VALUES
(N'[{"Level":"S1","Major":"TI"},{"Level":"NotSMA","Major":"IPA"}]'),
(N'[{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]'),
(N'[{"Level":"SMA","Major":"IPA"}]');
-- DDL and sample data population, end

DECLARE @separator CHAR(4) = '},{"'
    , @Level VARCHAR(20) = 'SMA';

SELECT * 
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r ' + 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Education, '":', '=')
        , ',"', SPACE(1))
        , '}]', '')
        , '[{"', ''), @separator, '/><r ') + 
        '/></root>' AS XML)) AS t1(c)
WHERE c.exist('/root/r[@Level=sql:variable("@Level")]') = 1;

Output

ID	Education
2	[{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3	[{"Level":"SMA","Major":"IPA"}]

Collectives™ on Stack Overflow

Using JSON DATA Without OPENJSON And Search by Value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related