create account

请MSSQL高手指点一下JSON的问题 by oflyhigh

View this thread on: hive.blog |  peakd.com |  ecency.com
cn · @oflyhigh ·
$278.55
请MSSQL高手指点一下JSON的问题
@jubi 小友今天与我探讨如何使用STEEMSQL查询符合条件的帖子用了哪些标签。

有关STEEMSQL的介绍以及一些我学习的记录，可以参考：
* [第一次使用STEEMSQL查询谷哥点名数据](https://steemit.com/cn/@oflyhigh/steemsql)
* [使用pymssql以及STEEMSQL的一点经验](https://steemit.com/cn/@oflyhigh/pymssql-steemsql)

![](https://steemitimages.com/DQmezDAf4ECH7M3Mu3FW5osgueg8CAW3cUEZWF4dRscxZVp/image.png)

# 读取标签

他问的这个问题，我并不以为是个难题，所以爽快的答应了下来。

在STEEMDATA中TAGS数据已经解析好了，我们可以直接拿到，我以为STEEMSQL也是如此，结果却并不是这么回事。Comments数据表中不存在TAGS字段，仅有json_metadata字段：
`json_metadata   varchar         0       -1`
以[这篇帖子](https://steemit.com/cn/@oflyhigh/pymssql-steemsql)为例, json_metadata数据是这个样子
![](https://steemitimages.com/DQmbwsjDj7742u7EqLg77D317C1b3HwdKLGGJw77TzXGXzL/image.png)
在数据库中作为字符串存储。


所以要把tags数据拿出来，我们需要对这部分数据进行解析，幸运的是SQL Server 从2016版本开始，加入了JSON的支持。
详情可以参考： [JSON Functions (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql)
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql

经过对上述网页以及其内相关链接的学习，我觉得我们应该用
`JSON_QUERY 	Extracts an object or an array from a JSON string.`

`cur.execute("SELECT title, JSON_QUERY(json_metadata, '$.tags') as tags FROM Comments WHERE author= 'oflyhigh' and title like N'%区块链%'")`

![](https://steemitimages.com/DQmV9edXeVbfHhM4wLs122uNyEyubrEmCKUvGsxitHmBeYh/image.png)
哪里不对？
我期望返回tags应该类似`['literature', 'poem', 'cn']`，这样的列表
结果却是：  `'["literature","poem","cn"]'`这样的字符串！吐血！

看这个文档：
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql
上边介绍说的是：`Extracts an object or an array from a JSON string. `
底下却又说：`Returns a JSON fragment of type nvarchar(max). `

例子中，返回的也是列表
![](https://steemitimages.com/DQmRCDVjvCWiSwYDmsouQnVinx1GErRLtpEKBabGhbErYqc/image.png)

到底应该返回啥，我有点方。

但是问题总得要解决是吧，SQL我尝试了各种方法实在没辙，我想到的方法就是在程序这端解决，比如这样：
![](https://steemitimages.com/DQmd8MtiCx4BMid1B4bGi5ReMQHxgFQxwY1HLiSW87AY2kR/image.png)


# 更进一步

这算是解答了 @jubi 小友的问题， 但是依然有以下疑问。

* 如何用SQL直接返回tags列表, 例如：`['literature', 'poem', 'cn']`
* 如果想查询包含指定tag的文章，该如何操作？比如查询包括`cn` tag 的文章。

因为json_metadata是字符串存储，所以对于问题二， jubi小友提出使用***`like '%cn%' `***或者正则匹配等方式，这也不失为一个好办法。或者我们还可以用***`JSON_QUERY(json_metadata, '$.tags') like '%cn%'`***缩小搜索范围，但是能否有精确匹配的查询方法呢？如果JSON_QUERY返回的是列表，我或许可以想些办法试试，但是返回的是字符串，我只好跪了。

期望各位程序大牛， SQL大牛不吝赐教，不胜感激。

# 参考文章
* https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql
* https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql
* https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
* https://docs.microsoft.com/en-us/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server
* https://www.sqlshack.com/native-json-support-in-sql-server-2016/
* https://blogs.oracle.com/jsondb/the-new-sqljson-query-operators-part2:-jsonquery  (From Oracle)
👍  , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , and 179 others
👎  
properties (23)
authoroflyhigh
permlinkmssql-json
categorycn
json_metadata{"tags":["cn","cn-programing","json","mssql","steemsql"],"users":["jubi"],"image":["https://steemitimages.com/DQmezDAf4ECH7M3Mu3FW5osgueg8CAW3cUEZWF4dRscxZVp/image.png","https://steemitimages.com/DQmbwsjDj7742u7EqLg77D317C1b3HwdKLGGJw77TzXGXzL/image.png","https://steemitimages.com/DQmV9edXeVbfHhM4wLs122uNyEyubrEmCKUvGsxitHmBeYh/image.png","https://steemitimages.com/DQmRCDVjvCWiSwYDmsouQnVinx1GErRLtpEKBabGhbErYqc/image.png","https://steemitimages.com/DQmd8MtiCx4BMid1B4bGi5ReMQHxgFQxwY1HLiSW87AY2kR/image.png"],"links":["https://steemit.com/cn/@oflyhigh/steemsql","https://steemit.com/cn/@oflyhigh/pymssql-steemsql","https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql","https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql","https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server","https://docs.microsoft.com/en-us/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server","https://www.sqlshack.com/native-json-support-in-sql-server-2016/","https://blogs.oracle.com/jsondb/the-new-sqljson-query-operators-part2:-jsonquery"],"app":"steemit/0.1","format":"markdown"}
created2017-08-15 14:43:18
last_update2017-08-15 14:43:18
depth0
children41
last_payout2017-08-22 14:43:18
cashout_time1969-12-31 23:59:59
total_payout_value236.474 HBD
curator_payout_value42.073 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length2,783
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,885,624
net_rshares80,901,355,015,152
author_curate_reward""
vote details (244)
@abupasi.alachy · 
A very interesting post and can be interested in my heart when I saw it greetings from my friends.
properties (22)
authorabupasi.alachy
permlinkre-oflyhigh-2017816t12484523z
categorycn
json_metadata{"tags":"cn","app":"esteem/1.4.6","format":"markdown+html","community":"esteem"}
created2017-08-16 05:48:09
last_update2017-08-16 05:48:09
depth1
children0
last_payout2017-08-23 05:48:09
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length98
author_reputation5,557,880,714,706
root_title请MSSQL高手指点一下JSON的问题
beneficiaries
0.
accountesteemapp
weight500
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,951,461
net_rshares0
@ace108 · (edited)
$8.37
我是用这样分开。
...
<pre><code>
JSON_VALUE(cm.json_metadata,'$.tags[0]') tag1,
JSON_VALUE(cm.json_metadata,'$.tags[1]') tag2,
JSON_VALUE(cm.json_metadata,'$.tags[2]') tag3,
JSON_VALUE(cm.json_metadata,'$.tags[3]') tag4,
JSON_VALUE(cm.json_metadata,'$.tags[4]') tag5
from Comments cm WITH (NOLOCK)
where ...
</code></pre>
也是不久前上网找到的。
👍  , , ,
properties (23)
authorace108
permlinkre-oflyhigh-mssql-json-20170815t151926237z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:19:27
last_update2017-08-15 15:20:12
depth1
children3
last_payout2017-08-22 15:19:27
cashout_time1969-12-31 23:59:59
total_payout_value6.277 HBD
curator_payout_value2.090 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length325
author_reputation1,232,654,309,126,963
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,888,872
net_rshares2,436,474,317,012
author_curate_reward""
vote details (4)
@ace108 · (edited)
$0.72
就好象我用于中日韩报告这样 [📊🌏中日韩龙虎榜自20170811 | CJK Top Pending Payout Post since 20170811 (by @ace108)](https://steemit.com/steemit-stats/@ace108/20170812-or-cjk-top-pending-payout-post-since-20170812-by-ace108)
补充...
<pre><code>
where
    ( ( JSON_VALUE(cm.json_metadata,'$.tags[4]') in ('cn','china') ) or   
      ( JSON_VALUE(cm.json_metadata,'$.tags[3]') in ('cn','china') ) or   
      ( JSON_VALUE(cm.json_metadata,'$.tags[2]') in ('cn','china') ) or   
      ( JSON_VALUE(cm.json_metadata,'$.tags[1]') in ('cn','china') ) or   
      ( JSON_VALUE(cm.json_metadata,'$.tags[0]') in ('cn','china') ) or   
</code></pre>
👍  ,
properties (23)
authorace108
permlinkre-ace108-re-oflyhigh-mssql-json-20170815t152438952z
categorycn
json_metadata{"tags":["cn"],"links":["https://steemit.com/steemit-stats/@ace108/20170812-or-cjk-top-pending-payout-post-since-20170812-by-ace108"],"app":"steemit/0.1"}
created2017-08-15 15:24:39
last_update2017-08-15 15:26:54
depth2
children0
last_payout2017-08-22 15:24:39
cashout_time1969-12-31 23:59:59
total_payout_value0.542 HBD
curator_payout_value0.181 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length612
author_reputation1,232,654,309,126,963
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,889,323
net_rshares211,016,453,087
author_curate_reward""
vote details (2)
@oflyhigh ·
$0.08
感谢分享，这个我有考虑过
对steemit而言也算是个好办法，毕竟标签数目不超过五个

你这连实现例子都给了
大大的👍
👍  
properties (23)
authoroflyhigh
permlinkre-ace108-re-oflyhigh-mssql-json-20170815t160104411z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 16:01:06
last_update2017-08-15 16:01:06
depth2
children1
last_payout2017-08-22 16:01:06
cashout_time1969-12-31 23:59:59
total_payout_value0.060 HBD
curator_payout_value0.019 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length59
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,892,698
net_rshares23,503,781,683
author_curate_reward""
vote details (1)
@ace108 · 
不客气。谢谢你的👍
👍  
properties (23)
authorace108
permlinkre-oflyhigh-re-ace108-re-oflyhigh-mssql-json-20170815t231851722z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 23:18:57
last_update2017-08-15 23:18:57
depth3
children0
last_payout2017-08-22 23:18:57
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length9
author_reputation1,232,654,309,126,963
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,926,079
net_rshares0
author_curate_reward""
vote details (1)
@ahsansaeed · 
Upvoted you! Good post.
properties (22)
authorahsansaeed
permlinkre-oflyhigh-mssql-json-20170816t123829177z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-16 12:38:27
last_update2017-08-16 12:38:27
depth1
children0
last_payout2017-08-23 12:38:27
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length23
author_reputation1,201,935,056,486
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,980,384
net_rshares0
@alvaroquenta · 
mucho contenido mi hermano te felicito
properties (22)
authoralvaroquenta
permlinkre-oflyhigh-mssql-json-20170815t184514404z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 18:45:12
last_update2017-08-15 18:45:12
depth1
children0
last_payout2017-08-22 18:45:12
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length38
author_reputation1,397,945,467,598
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,907,040
net_rshares0
@arcange · 
Congratulations @oflyhigh!
Your post was mentioned in the [hit parade](https://steemit.com/hit-parade/@arcange/daily-hit-parade-20170815) in the following category:

* Pending payout - Ranked 5 with $ 275,89
properties (22)
authorarcange
permlinkre-mssql-json-20170815t163857000z
categorycn
json_metadata""
created2017-08-16 14:38:57
last_update2017-08-16 14:38:57
depth1
children0
last_payout2017-08-23 14:38:57
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length208
author_reputation1,148,326,388,038,146
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,991,570
net_rshares0
@balcheng · 
아주 좋은 포스트 @oflyhigh
나를 @balcheng 따라 + 찬성 투표하십시오
properties (22)
authorbalcheng
permlinkre-oflyhigh-2017815t21508891z
categorycn
json_metadata{"tags":"cn","app":"esteem/1.4.6","format":"markdown+html","community":"esteem"}
created2017-08-15 14:50:15
last_update2017-08-15 14:50:15
depth1
children0
last_payout2017-08-22 14:50:15
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length47
author_reputation227,599,409,731
root_title请MSSQL高手指点一下JSON的问题
beneficiaries
0.
accountesteemapp
weight500
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,886,281
net_rshares0
@berniesanders ·
$1.00
@abit and @ned you guys are **DESTROYING** Steemit by upvoting this fucking trash and rewarding multiple $ per **VIEW** just because the posts are Asian.

This is fucking bullshit and you know it.  You're contributing to #rewardpoolrape and I urge you to stop this bullshit immediately.

**For the rest of the community, YOU SHOULD BE FLAGGING THIS SHIT, this is EXACTLY why the flags were added.  Do NOT let @ned and @abit destroy what has been built here.  Use your flags!!**
👍  
properties (23)
authorberniesanders
permlinkre-oflyhigh-mssql-json-20170815t191508706z
categorycn
json_metadata{"tags":["rewardpoolrape","cn"],"users":["abit","ned"],"app":"steemit/0.1"}
created2017-08-15 19:15:09
last_update2017-08-15 19:15:09
depth1
children0
last_payout2017-08-22 19:15:09
cashout_time1969-12-31 23:59:59
total_payout_value0.948 HBD
curator_payout_value0.047 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length477
author_reputation600,251,775,828,524
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,909,417
net_rshares291,306,830,899
author_curate_reward""
vote details (1)
@joythewanderer · (edited)
$2.32
json_metadata LIKE '%"cn"%' 把cn隔开？避免出现例如"canton"这样包含cn的单词？
👍  , , ,
properties (23)
authorjoythewanderer
permlinkre-oflyhigh-mssql-json-20170815t145228193z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 14:52:24
last_update2017-08-15 14:54:51
depth1
children1
last_payout2017-08-22 14:52:24
cashout_time1969-12-31 23:59:59
total_payout_value1.901 HBD
curator_payout_value0.420 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length58
author_reputation1,916,082,145,948,706
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,886,484
net_rshares675,277,275,794
author_curate_reward""
vote details (4)
@oflyhigh ·
$0.08
感谢回复，如果能直接查就最好了
Json支持还是mongodb强大啊
👍  
properties (23)
authoroflyhigh
permlinkre-joythewanderer-re-oflyhigh-mssql-json-20170815t151342499z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:13:42
last_update2017-08-15 15:13:42
depth2
children0
last_payout2017-08-22 15:13:42
cashout_time1969-12-31 23:59:59
total_payout_value0.057 HBD
curator_payout_value0.019 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length34
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,888,365
net_rshares22,544,443,655
author_curate_reward""
vote details (1)
@kenchung · 
不懂，留名學習
properties (22)
authorkenchung
permlinkre-oflyhigh-mssql-json-20170815t153727801z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:37:21
last_update2017-08-15 15:37:21
depth1
children2
last_payout2017-08-22 15:37:21
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length7
author_reputation41,181,348,504,685
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,890,450
net_rshares0
@kitcat · 
不會懂，留名看你們學習
properties (22)
authorkitcat
permlinkre-kenchung-re-oflyhigh-mssql-json-20170815t154026249z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:40:27
last_update2017-08-15 15:40:27
depth2
children0
last_payout2017-08-22 15:40:27
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length11
author_reputation33,063,379,747,116
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,890,748
net_rshares0
@oflyhigh · 
老师太谦虚了
properties (22)
authoroflyhigh
permlinkre-kenchung-re-oflyhigh-mssql-json-20170815t160249958z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 16:02:51
last_update2017-08-15 16:02:51
depth2
children0
last_payout2017-08-22 16:02:51
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length6
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,892,878
net_rshares0
@lydiachan · 
二師兄近期的文章都好高深，妹紙都已看不見車尾燈了… T_T
properties (22)
authorlydiachan
permlinkre-oflyhigh-mssql-json-20170816t111746185z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-16 11:17:48
last_update2017-08-16 11:17:48
depth1
children0
last_payout2017-08-23 11:17:48
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length29
author_reputation38,600,108,764,289
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,973,793
net_rshares0
@mianfahad · 
i upvoted your post plz upvote me back!!
👍  
properties (23)
authormianfahad
permlinkre-oflyhigh-mssql-json-20170815t151440378z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:14:45
last_update2017-08-15 15:14:45
depth1
children0
last_payout2017-08-22 15:14:45
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length40
author_reputation-548,955,562,576
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,888,454
net_rshares0
author_curate_reward""
vote details (1)
@mitapeng ·
$0.38
Pu ka kahah qe hana kutupu meusikrek pih...
Man kupeugah chit sang bereh..
Bereh that tulesan droe neuh hy @oflyhigh eukk, lage lam kapai are.
Nyoe kan ku tuleh nyoe koen pu laen, nyoe ku meuharap meurumpok persen bacut bak si karma, meunyoe hana jih jok persen meu bacut theh sang hana so jok keudroe kuh, jadi nyoe komen lon numpang jak siat, meuah beuh, bek neukira apam syara beh, trimong geunaseh, mnyoe awak blah deuh khen thank you very much, nyan ban :)
👍  , , , , , , , , , , , , ,
properties (23)
authormitapeng
permlinkre-oflyhigh-2017816t7101560z
categorycn
json_metadata{"tags":"cn","app":"esteem/1.4.6","format":"markdown+html","community":"esteem"}
created2017-08-16 00:10:06
last_update2017-08-16 00:10:06
depth1
children1
last_payout2017-08-23 00:10:06
cashout_time1969-12-31 23:59:59
total_payout_value0.368 HBD
curator_payout_value0.013 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length461
author_reputation-120,589,769,878
root_title请MSSQL高手指点一下JSON的问题
beneficiaries
0.
accountesteemapp
weight500
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,929,584
net_rshares114,679,611,896
author_curate_reward""
vote details (14)
@lurehound · 
GREAT award winning post! Follow me @lurehound
properties (22)
authorlurehound
permlinkre-mitapeng-re-oflyhigh-2017816t7101560z-20170825t013935485z
categorycn
json_metadata{"tags":["cn"],"users":["lurehound"],"app":"steemit/0.1"}
created2017-08-25 01:39:36
last_update2017-08-25 01:39:36
depth2
children0
last_payout2017-09-01 01:39:36
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length46
author_reputation1,059,129,875,621
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id12,794,294
net_rshares0
@mstsreety · 
Nice Post
properties (22)
authormstsreety
permlinkre-oflyhigh-mssql-json-20170815t154635644z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:46:42
last_update2017-08-15 15:46:42
depth1
children0
last_payout2017-08-22 15:46:42
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length9
author_reputation2,987,864,138
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,891,366
net_rshares0
@powerfj · 
用like的话, 如果数据量大的时候, 数据库应该要跪掉.. 如果要保证性能,  可能有索引最好
properties (22)
authorpowerfj
permlinkre-oflyhigh-mssql-json-20170815t162801055z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 16:28:00
last_update2017-08-15 16:28:00
depth1
children0
last_payout2017-08-22 16:28:00
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length48
author_reputation1,712,765,353,203
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,895,216
net_rshares0
@rajorajiten · 
json_metadata LIKE'％“cn”％“把cn隔开？避免出现例如”canton“这样包含cn的单词？这是非常感兴趣的话题和巨大的收入
properties (22)
authorrajorajiten
permlinkre-oflyhigh-mssql-json-20170815t174736551z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 17:47:45
last_update2017-08-15 17:47:45
depth1
children0
last_payout2017-08-22 17:47:45
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length72
author_reputation15,014,449,393,952
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,902,289
net_rshares0
@rajorajiten · 
JSON_VALUE(cm.json_metadata,'$.tags[0]') tag1,
JSON_VALUE(cm.json_metadata,'$.tags[1]') tag2,
JSON_VALUE(cm.json_metadata,'$.tags[2]') tag3,
JSON_VALUE(cm.json_metadata,'$.tags[3]') tag4,
JSON_VALUE(cm.json_metadata,'$.tags[4]') tag5
from Comments cm WITH (NOLOCK)
where ...
properties (22)
authorrajorajiten
permlinkre-oflyhigh-mssql-json-20170815t174900643z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 17:49:09
last_update2017-08-15 17:49:09
depth1
children0
last_payout2017-08-22 17:49:09
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length274
author_reputation15,014,449,393,952
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,902,392
net_rshares0
@sagebasry · 
呼吸你的灵魂，你在我的海上海滩
这颗内心的是一个圣洁的爱
我打算去死
只爱你
渴望我的灵魂我爱
我的灵魂与我交配
要死，我想和你在一起
这是我的观点
我的灵魂伴侣想要你
一个爱死亡分离
你和我是一个
爱着对方
在我悲伤的悲伤之后
人生现在是你的
👍  
properties (23)
authorsagebasry
permlinkre-oflyhigh-2017816t174827869z
categorycn
json_metadata{"tags":"cn","app":"esteem/1.4.6","format":"markdown+html","community":"esteem"}
created2017-08-16 10:48:30
last_update2017-08-16 10:48:30
depth1
children0
last_payout2017-08-23 10:48:30
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length122
author_reputation80,408,080,749
root_title请MSSQL高手指点一下JSON的问题
beneficiaries
0.
accountesteemapp
weight500
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,971,643
net_rshares414,568,490
author_curate_reward""
vote details (1)
@shieha · 
SQL 真的不是我的强項，無法幫忙 ><
properties (22)
authorshieha
permlinkre-oflyhigh-mssql-json-20170816t130141475z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-16 13:01:39
last_update2017-08-16 13:01:39
depth1
children2
last_payout2017-08-23 13:01:39
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length20
author_reputation155,543,464,535,862
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,982,292
net_rshares0
@oflyhigh · 
谢啦
properties (22)
authoroflyhigh
permlinkre-shieha-re-oflyhigh-mssql-json-20170816t140734303z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-16 14:07:36
last_update2017-08-16 14:07:36
depth2
children1
last_payout2017-08-23 14:07:36
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length2
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,988,272
net_rshares0
@shieha · (edited)
還要跟你們學習呢~
properties (22)
authorshieha
permlinkre-oflyhigh-re-shieha-re-oflyhigh-mssql-json-20170817t063602976z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-17 06:36:03
last_update2017-08-17 06:36:09
depth3
children0
last_payout2017-08-24 06:36:03
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length9
author_reputation155,543,464,535,862
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id12,057,704
net_rshares0
@steemresteem · 
# YOUR POST WAS IN TRENDING! UPPED and RESTEEMED
https://steemitimages.com/DQmNRxGArjqx7ifhADyUyC9g2xiihKTr8dPnWP2tENqFFJe/steemresteem%20.png
properties (22)
authorsteemresteem
permlinkre-oflyhigh-mssql-json-20170816t122345065z
categorycn
json_metadata{"tags":["cn"],"image":["https://steemitimages.com/DQmNRxGArjqx7ifhADyUyC9g2xiihKTr8dPnWP2tENqFFJe/steemresteem%20.png"],"app":"steemit/0.1"}
created2017-08-16 12:23:45
last_update2017-08-16 12:23:45
depth1
children0
last_payout2017-08-23 12:23:45
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length142
author_reputation4,677,180,865
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,979,158
net_rshares0
@taoufik · 
Thanks for this post
properties (22)
authortaoufik
permlinkre-oflyhigh-mssql-json-20170815t170201494z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 17:02:03
last_update2017-08-15 17:02:03
depth1
children0
last_payout2017-08-22 17:02:03
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length20
author_reputation203,549,071,834
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,898,362
net_rshares0
@tngflx · 
why so many people upvoted this post? just because of some technical advice needed??? hmmm
properties (22)
authortngflx
permlinkre-oflyhigh-mssql-json-20170815t151604952z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:16:03
last_update2017-08-15 15:16:03
depth1
children0
last_payout2017-08-22 15:16:03
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length90
author_reputation17,396,455,988,713
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,888,583
net_rshares0
@tumutanzi · 
虽然不懂这些技术，还是为你们感到自豪。
properties (22)
authortumutanzi
permlinkre-oflyhigh-mssql-json-20170815t145149768z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 14:51:51
last_update2017-08-15 14:51:51
depth1
children3
last_payout2017-08-22 14:51:51
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length19
author_reputation193,724,901,968,179
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,886,425
net_rshares0
@coldhair · 
同上
properties (22)
authorcoldhair
permlinkre-tumutanzi-re-oflyhigh-mssql-json-20170815t151232934z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:12:30
last_update2017-08-15 15:12:30
depth2
children0
last_payout2017-08-22 15:12:30
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length2
author_reputation34,617,352,014,488
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,888,272
net_rshares0
@oflyhigh · 
没啥的 😄
properties (22)
authoroflyhigh
permlinkre-tumutanzi-re-oflyhigh-mssql-json-20170815t151435089z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 15:14:36
last_update2017-08-15 15:14:36
depth2
children1
last_payout2017-08-22 15:14:36
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length5
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,888,435
net_rshares0
@superhardness · 
我只能做出崇拜状，看着你们发挥。
properties (22)
authorsuperhardness
permlinkre-oflyhigh-re-tumutanzi-re-oflyhigh-mssql-json-20170817t005841058z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-17 00:58:42
last_update2017-08-17 00:58:42
depth3
children0
last_payout2017-08-24 00:58:42
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length16
author_reputation16,710,361,233,315
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id12,037,468
net_rshares0
@tvb · (edited)
好像在PHP可以把这串字符转到数组再读数据是不是？我也不太懂。我前在 @justyy 的贴“ https://steemit.com/cn/@justyy/steem-api-transfer-history-ifttt-slack-how-to-use-steem-api-transfer-history-and-ifttt-to-sync-to-slack” 里看到json_decode()函数，好像可以。但如果是mssql环境下，没用过，基本不懂了。。。我等着学习吧
properties (22)
authortvb
permlinkre-oflyhigh-mssql-json-20170815t152622053z
categorycn
json_metadata{"tags":["cn"],"users":["justyy"],"links":["https://steemit.com/cn/@justyy/steem-api-transfer-history-ifttt-slack-how-to-use-steem-api-transfer-history-and-ifttt-to-sync-to-slack”"],"app":"steemit/0.1"}
created2017-08-15 15:26:30
last_update2017-08-15 15:28:03
depth1
children1
last_payout2017-08-22 15:26:30
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length238
author_reputation35,315,140,618,944
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,889,474
net_rshares0
@oflyhigh · 
额
properties (22)
authoroflyhigh
permlinkre-tvb-re-oflyhigh-mssql-json-20170815t160229767z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-15 16:02:30
last_update2017-08-15 16:02:30
depth2
children0
last_payout2017-08-22 16:02:30
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length1
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,892,850
net_rshares0
@yuxi ·
$0.96
@oflyhigh 你的SQL没有问题，可能是你的python脚本有点小问题。

如果运行以下程序，会看到和你所说一样的输出：

cur.execute("SELECT title, JSON_QUERY(json_metadata, '$.tags') as tags FROM Comments WHERE author= 'oflyhigh' and title like N'%区块链%'")
rows = cur.fetchall()
for row in rows: print(row)

其实，问题就出在哪个输出语句了，稍作改动：

for row in rows: print(row[0],row[1])

这是输出结果：

我也曾经文青过，18年前写的小诗 《花儿》，学laoyao，咱也放到区块链上 ["literature","poem","cn"]
使用PHP查询STEEM区块链 / Using PHP to query the STEEM blockchain ["cn","cn-programming","steemdev","php","steem"]
珍惜羽毛 / STEEM区块链忠实的记录你的操作 / 获得共同操作账户的真实操作者 ["cn","cn-programming","steem","steemit"]
YY 一个基于STEEM区块链的聊天工具 ["cn","cn-programming"]
STEEM区块链忠实的记录你的言行&操作 ["cn"]
👍  
properties (23)
authoryuxi
permlinkre-oflyhigh-mssql-json-20170816t143352062z
categorycn
json_metadata{"tags":["cn"],"users":["oflyhigh"],"app":"steemit/0.1"}
created2017-08-16 14:33:54
last_update2017-08-16 14:33:54
depth1
children4
last_payout2017-08-23 14:33:54
cashout_time1969-12-31 23:59:59
total_payout_value0.723 HBD
curator_payout_value0.241 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length652
author_reputation11,383,558,615,223
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,991,048
net_rshares272,447,130,698
author_curate_reward""
vote details (1)
@oflyhigh · 
谢谢答复
不过你这样只不过是把tags列表当成字符串输出了 😄，尽管看起来是列表的样子
我想要的是一个列表

你试着输出row[1][0]试试就明白了
properties (22)
authoroflyhigh
permlinkre-yuxi-re-oflyhigh-mssql-json-20170816t153745439z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-16 15:37:45
last_update2017-08-16 15:37:45
depth2
children3
last_payout2017-08-23 15:37:45
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length75
author_reputation6,390,297,909,157,269
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,997,044
net_rshares0
@yuxi · 
对MSSQL这个功能不太了解。不过从数据库那里不应该直接返回object，因为object是和具体编程语言相关的。你想要得到json object也没问题，用下面的python code:

for row in rows: print(row[0],json.loads(row[1]),json.loads(row[1])[0])
properties (22)
authoryuxi
permlinkre-oflyhigh-re-yuxi-re-oflyhigh-mssql-json-20170816t161838392z
categorycn
json_metadata{"tags":["cn"],"app":"steemit/0.1"}
created2017-08-16 16:18:39
last_update2017-08-16 16:18:39
depth3
children2
last_payout2017-08-23 16:18:39
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length167
author_reputation11,383,558,615,223
root_title请MSSQL高手指点一下JSON的问题
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id12,000,529
net_rshares0
Help/Feedback